Java PreparedStatement使用Timestamp.from(Instant)时读取本地时区。

huangapple go评论67阅读模式
英文:

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

问题

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.

英文:

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.

答案1

得分: 3

OffsetDateTime

>当我使用Instant.now()获取当前的UTC时间时,

不要在SQL数据库工作中使用Instant。在JDBC 4.2+中,规范将OffsetDateTime类映射到类似于SQL标准类型TIMESTAMP WITH TIME ZONE的列。

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

InstantZonedDateTime都不在JDBC中映射。SQL标准未定义与这些类相当的类型。

顺便说一下,对于类似于SQL标准类型TIMESTAMP WITHOUT TIME ZONE的列,请使用LocalDateTime类。

避免使用旧的日期时间类

>将Timestamp.from(instant)的转换

永远不要使用糟糕的旧日期时间类,比如Timestamp。只使用其替代品:在JSR 310中定义的现代java.time类。

写入数据库:

myPreparedStatement.setObject(, odt);

检索:

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

不要依赖默认时区

您评论说:

>问题是我的JVM默认为本地时区

您应该编写Java代码,使其不关心JVM的当前默认时区。

上面显示的代码不受JVM当前默认时区的影响。

示例代码

这是一个完整的示例。

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);

        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 a 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.println("-------------|  table end  |---------------");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

运行时:

-------------|  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
英文:

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

答案2

得分: 0

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.

英文:

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.

huangapple
  • 本文由 发表于 2023年2月10日 03:39:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/75403642.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定