Java PreparedStatement reading local timezone when using Timestamp.from(Instant)

Question

I'm attempting to populate a UTC timestamp into a SQL table, but when I use Instant.now() to get the current UTC time, a conversion of Timestamp.from(instant) is writing local time zones into the table. Is there a way to write UTC into the table?

PreparedStatement ps = connection.prepareStatement(...)
ps.setString(1, Timestamp.from(Instant.now())

This results in local timezone opposed to UTC.

The JDBC driver is net.sourceforge.jtds.jdbc.Driver.

Answer 1

Changing the JVM timezone to UTC solved the issue of Timestamp, however, in this case I cannot update the server in which this code is running.

The solution that worked for me without needing to adjust JVM timezone is

statement.setTimestamp(1,
        Timestamp.valueOf(DateTimeFormatter.ofPattern("YYYY-MM-dd HH:mm:ss.SSS")
                .format(Instant.now().atZone(ZoneId.of("UTC")))));

This forces Timestamp to use UTC, although it seems rather robust and redundant. The other solutions proposed did not work for me.

Answer 2

OffsetDateTime

when I use Instant.now() to get the current UTC time,

Don’t use Instant for SQL database work.

In JDBC 4.2+, the specification maps OffsetDateTime class to columns of a type akin to the SQL standard type TIMESTAMP WITH TIME ZONE.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;

Neither Instant nor ZonedDateTime are mapped in JDBC. The SQL standard defines no such types equivalent to those classes.

By the way, for columns of a type akin to the SQL standard type TIMESTAMP WITHOUT TIME ZONE, use the LocalDateTime class.

Avoid legacy date-time classes

a conversion of Timestamp.from(instant)

Never use the terrible legacy date-time classes such as Timestamp. Use only their replacement: the modern java.time classes defined in JSR 310.

Write to the database:

myPreparedStatement.setObject … , odt ) ;

Retrieve:

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

Do not depend on default zone

You commented:

The issue was my JVM was defaulting to local time

You should write your Java code in such a way as to not care about the JVM’s current default time zone.

The code shown above is unaffected by the JVM’s current default time zone.

Example code

Here is a complete example.

package work.basil.example.db;

import javax.sql.DataSource;
import java.sql.*;
import java.time.Instant;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.util.UUID;

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

    private void demo ( )
    {
        DataSource dataSource = this.fetchDataSource();
        this.createTable( dataSource );
        this.insertDummyData( dataSource );
        this.dump( dataSource );

//        Scanner scanner = new Scanner( System.in );
//        System.out.print( "Type anything to end program: " );
//        String anything = scanner.nextLine();
        System.out.println( "Demo done at " + Instant.now() );
    }

    private DataSource fetchDataSource ( )
    {
        org.h2.jdbcx.JdbcDataSource dataSource = new org.h2.jdbcx.JdbcDataSource();
        dataSource.setURL( "jdbc:h2:mem:demo_db;DB_CLOSE_DELAY=-1" );
        return dataSource;
    }

    private void createTable ( final DataSource dataSource )
    {
        String sql =
                """
                CREATE TABLE bogus_ (
                    id_ UUID PRIMARY KEY  ,
                    when_ TIMESTAMP WITH TIME ZONE
                )
                ;
                """;
        try (
                Connection conn = dataSource.getConnection() ;
                Statement stmt = conn.createStatement() ;
        )
        {
            stmt.execute( sql );
        }
        catch ( SQLException e ) { throw new RuntimeException( e ); }
    }

    private void insertDummyData ( final DataSource dataSource )
    {
        String sql =
                """
                INSERT INTO bogus_ ( id_ , when_ )
                VALUES ( ? , ? )
                ;
                """;
        try (
                Connection conn = dataSource.getConnection() ;
                PreparedStatement pstmt = conn.prepareStatement( sql ) ;
        )
        {
            pstmt.setObject( 1 , UUID.fromString( "97a9e379-4d8f-4d06-8bea-43560a72120b" ) );
            pstmt.setObject( 2 , OffsetDateTime.now( ZoneOffset.UTC ) );
            pstmt.executeUpdate();

            pstmt.setObject( 1 , UUID.fromString( "052ae129-d0ca-4fdf-9a06-c87d20a2d3f2" ) );
            pstmt.setObject( 2 , OffsetDateTime.now( ZoneOffset.UTC ) );
            pstmt.executeUpdate();
        }
        catch ( SQLException e ) { throw new RuntimeException( e ); }
    }

    private void dump ( final DataSource dataSource )
    {
        String sql =
                """
                SELECT * FROM bogus_
                ;
                """;
        try (
                Connection conn = dataSource.getConnection() ;
                Statement stmt = conn.createStatement() ;
                ResultSet resultSet = stmt.executeQuery( sql ) ;
        )
        {

            System.out.println( "-------------|  table start |---------------" );
            while ( resultSet.next() )
            {
                UUID uuid = resultSet.getObject( "id_" , UUID.class );
                OffsetDateTime when = resultSet.getObject( "when_" , OffsetDateTime.class );
                System.out.println( uuid + " | " + when );
            }
            System.out.println( "-------------|  table end  |---------------" );
        }
        catch ( SQLException e ) { throw new RuntimeException( e ); }
    }
}

When run:

-------------|  table start |---------------
97a9e379-4d8f-4d06-8bea-43560a72120b | 2023-02-10T20:32:57.074979Z
052ae129-d0ca-4fdf-9a06-c87d20a2d3f2 | 2023-02-10T20:32:57.080153Z
-------------|  table end  |---------------
Demo done at 2023-02-10T20:32:57.092230Z