英文:
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);
Instant
和ZonedDateTime
都不在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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论