How to prevent date change using H2 for a JUnit-Test?

Question

For a JUnit-Test using H2 I am saving an entity with a LocalDate property and the value LocalDate.parse("1900-01-01"). The test fails with

    Expected: is <1900-01-01>
    but: was <1899-12-31>

LocalDate does not care about timezone, so I guess the change is made during the saving-action at the H2 database.

I already tried to set the timezone UTC, since that should be the timezone for the H2 and then the H2 should not interpret the date it is handed over as having another timezone that has to be converted to UTC. But that did not seem to help.

    public static void main(final String[] args) {
        TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
        SpringApplication.run(BackendApplication.class, args);
    }

Thanks in advance for suggestions and ideas

Answer 1

Cannot reproduce your test failure

Your code:

LocalDate.parse( "1900-01-01" )

…works as documented, producing a value of January 1, 1900.

LocalDate ld = LocalDate.parse("1900-01-01") ;
System.out.println( ld ) ;

See that code run live at IdeOne.com.

1900-01-01

? There must be more to your code than you showed us.

Example app using H2

You said:

I guess the change is made during the saving-action at the H2 database.

Let's try it. Here is an entire example app using H2 version 1.4.200. Ran from IntelliJ 2020.1 on macOS Mojave with Java 14.

We have a table event_ with an id_ column of type UUID, and a column when_ of type LocalDate. We write a single value for a value of 1900-01-01.

As you can see if you run this code, we get back a LocalDate object for which the method toString returns 1900-01-01.

package work.basil.example;

import org.h2.jdbcx.JdbcDataSource;

import java.sql.*;
import java.time.LocalDate;
import java.util.Objects;
import java.util.UUID;

public class H2Example
{
    public static void main ( String[] args )
    {
        H2Example app = new H2Example();
        app.doIt();
    }

    private void doIt ( )
    {
        JdbcDataSource dataSource = Objects.requireNonNull( new JdbcDataSource() );  // Implementation of `DataSource` bundled with H2.
        dataSource.setURL( "jdbc:h2:mem:localdate_example_db;DB_CLOSE_DELAY=-1" ); // Set `DB_CLOSE_DELAY` to `-1` to keep in-memory database in existence after connection closes.
        dataSource.setUser( "scott" );
        dataSource.setPassword( "tiger" );

        String sql = null;

        try (
                Connection conn = dataSource.getConnection() ;
        )
        {
            String tableName = "event_";
            sql = "CREATE TABLE " + tableName + " (\n" +
                    "  id_ UUID DEFAULT random_uuid() PRIMARY KEY ,\n" +
                    "  when_ DATE NOT NULL\n" +
                    ");";
            try ( Statement stmt = conn.createStatement() ; )
            {
                stmt.execute( sql );
            }

            // Insert row.
            sql = "INSERT INTO event_ ( when_ ) " + "VALUES ( ? ) ;";
            try (
                    PreparedStatement preparedStatement = conn.prepareStatement( sql ) ;
            )
            {
                LocalDate localDate = LocalDate.parse( "1900-01-01" );
                preparedStatement.setObject( 1 , localDate );  // Yesterday.
                preparedStatement.executeUpdate();
            }

            // Query all.
            sql = "SELECT * FROM event_ ;";
            try (
                    Statement stmt = conn.createStatement() ;
                    ResultSet rs = stmt.executeQuery( sql ) ;
            )
            {
                while ( rs.next() )
                {
                    //Retrieve by column name
                    UUID id = rs.getObject( "id_" , UUID.class );  // Pass the class to be type-safe, rather than casting returned value.
                    LocalDate localDate = rs.getObject( "when_" , LocalDate.class );  // Ditto, pass class for type-safety.

                    //Display values
                    System.out.println( "id_: " + id + " | when_: " + localDate );
                }
            }
        }
        catch ( SQLException e )
        {
            e.printStackTrace();
        }
    }
}

When that example app runs:

id_: 949830e0-77c8-49a3-8804-0972ff70ca2c | when_: 1900-01-01

---

Do not rely on default time zone

Your code:

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

…is quite risky at deployment. Setting the time zone immediately affects all code in all threads of all apps running within that JVM. You could be pulling out the carpet from beneath the feet of other apps, or ever other parts of your own app.

? Better to never rely on the JVM’s current default time zone.

To capture the current moment as seen in UTC, simply call Instant.now. An Instant represents a moment in UTC with a resolution of nanoseconds. This class replaces java.util.Date.

Instant instant = Instant.now() ;  // Capture the current moment in UTC.

For database work, use the more flexible class OffsetDateTime. The JDBC 4.2 specification inexplicably requires support for OffsetDateTime but not the more commonly used Instant and ZonedDateTime.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;  // Capture the current moment in UTC.

Write that to the database.

myPreparedStatement.setObject( … , odt ) ;

Retrieval.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

When recording a moment (a specific point on the timeline) such as that represented by OffsetDateTime, your column in the database must be of a data type akin to the SQL standard type TIMESTAMP WITH TIME ZONE (not WITHOUT). In H2, the data type to use has that very name, TIMESTAMP WITH TIME ZONE.

Answer 2

Short answer: I had a similar Problem and removing TimeZone.setDefault() from some @PostConstruct-method solved it.

But there is a lot more to say.

Configuration

a JUnit-Test using H2

I guess your production environment is not H2 so my first recommendation is to use one of the available compatibility modes:

H2 Features: Compatibility

Some modes make assumptions about the appropriate Date type. The Oracle Compatibility Mode for example treats the DATE data type like TIMESTAMP(0) data type.

Other possible solutions:

• You could try the Hibernate property hibernate.jdbc.time_zone.
• Some database drivers allow to set the time zone as part of the JDBC-Url.

Logging

LocalDate does not care about timezone, so I guess the change is made during the saving-action at the H2 database.

Your test needs to read the value from the database to be able to compare it to the expected value. Therefore you can not be sure that the conversion is within the saving-action, it could also be the reading as suggested in this article by Martín Lamas.

And it doesn't has to be the H2 code that is causing the trouble. At least You will use some jdbc driver and if you are using JPA there also is the JPA-Provider.

So my second recommendation is to log helpful information. You might want to use Trace Options and take inspirations from the Logging Guide for Hibernate by Thorben Janssen.

If you enable H2 Trace Option and Hibernate Tracing You might see something like the following lines in Your logs when saving some LocalDate:

Hibernate log:

... BasicBinder:64 - binding parameter [...] as [DATE] - [...]

H2 log:

/**/prep6.setDate(..., Date.valueOf("..."));

If You read some Date columns the log might contain something like the following lines.

H2 log:

/**/rs65.getDate("...");

Hibernate log:

... BasicExtractor:60 - extracted value ([...] : [DATE]) - [...]

Debugging

In the previous example the H2 code is using java.sql.Date.valueOf() during the save-action and org.h2.jdbc.JdbcResultSet.getDate() while reading.

My third recommendation is that You put breakpoints in these methods and debug what is happening. I expect You to find out that org.h2.util.LegacyDateTimeUtils.toDate() is used whith no given time zone and some cached TimeZoneProvider will use ZoneId.systemDefault() to get the local time zone.

So there is some legacy code in place, even if You use modern data types such as LocalDate. TimeZone.setDefault(...) will have effects on this code.

Solution

Setting the default TimeZone programmatically is an option, but it can make things worse.

Let's say that You develop and test Your code on some Window system in the TimeZone Europe/Berlin and everything looks fine. If You run the same tests as part of a test-pipeline in some docker container, there is a good chance that default TimeZone will be UTC as this is the setting in most linux base images. If you still want to use Europe/Berlin, You need to set it early enough to reduce any side effects. The earliest possible point I know of is the -Duser.timezone System Property (see Time Zone Settings in the JRE).

So here is my final recommendation: Prefer -Duser.timezone System Property over TimeZone.setDefault().

Answer 3

Are you creating a lot of pages? Or do you already have a lot of existing pages and want to change them?

1) Create pages (from data) with a static site builder

A static site builder is a program you run locally on your computer. You can read the data from a database or JSON files, or some API. You write some templates and some logic, then run the site builder, and many HTML documents are created.

These HTMl documents you can then upload to webspace somewhere.

If you already have a favorite programming language, you can find a static site builder in your favorite language. Here's a list of different static site builders

2) Create pages (from data) with a backend programming language

With a backend programming language the html pages are created "on the fly", when a browser tries to access them. You can read the data from a database or JSON files or APIs. You write some templates and some logic, then upload the programm to a webspace that supports your language.

If you already have a favorite language you can probably use it: PHP, JavaScript, Ruby, Python, ... they can all be used in this way. And you can find tutorials and online courses for all of them.

3) Create pages from existing HTML pages with a web scaper

If you are dealing with a lot of pre-existing HTML-Pages that you want to change (translate?) you can use a tool for webscraping. The tool will help you to analyse the existing pages, do some changes on them, and then write out new pages.

I can recommend mechanize in Ruby, but I'm sure there are equivalent libraries in other languages.