postgresql JDBC/Postgres如何比较无时区java。util。有时间戳的日期？

Question

我们有一个Postgres表，它有两个^{}列，prc_stau__dt和prc_end_dt。我们检查java.util.Date是否介于开始日期和结束日期之间

下面是一些Java代码，虽然经过了简化，但却能理解要点

// This format expects a String such as 2018-12-03T10:00:00
// With a date and a time, but no time zone

String timestamp = "2018-12-03T10:00:00";
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
Date searchDate = formatter.parse(timestamp);

// Here's the Postgres query

String query = "select promotion_cd from promotions " + 
               "where prc_sta_dt <= :srch_dt and prc_end_dt >= :srch_dt";

Map<String, Object> map = new HashMap<String, Object>();
map.put("srch_dt", searchDate);

List<Promotion> promotions = jdbcTemplate.query(query, map, promotionMapper);

在我们的Postgres表中，我们的促销活动从2018年12月3日上午9点开始，到当天下午3点结束。我们数据库中这些行的prc_stau__dt和prc_end_dt列是2018-12-03 09:00:00.0和2018-12-03 15:00:00.0

问题：当JDBC/Postgres接受我们的searchDate并将其与这些时间戳进行比较时，它会接受给定的搜索日期上午10点（2018-12-03T10:00:00），还是将此时间视为服务器运行的时区，然后将其转换为UTC

例如，如果服务器在芝加哥运行，那么它是否会将上午10点解释为CST上午10点，然后在数据库中进行比较之前将其转换为UTC下午4点？如果是这样，那我们就倒霉了

我怀疑这会发生，但我只是想确保没有意外

Answer 1

错误的数据类型，Date不是日期

一个java.util.Date对象表示UTC中的一个时刻，即时间线上的一个特定点。因此，它表示一个日期、一天中的一个时间和UTC的零偏移量（对于UTC本身）的组合。在这个糟糕的类中，有许多糟糕的设计选择，其中之一就是它误导性的名称，它让无数Java程序员感到困惑

TIMESTAMP WITHOUT TIME ZONE

如果您关心时刻，那么您的数据库列应该是而不是类型TIMESTAMP WITHOUT TIME ZONE。该数据类型表示一天中的一个日期和时间，没有任何时区或UTC偏移的概念。因此，根据定义，该类型不能代表一个时刻，不是时间线上的一个点。仅当您指的是时间在任何地方或在任何地方的日期时，才应使用此类型

示例：

• “2018年12月25日午夜钟声敲响后，圣诞节就开始了”，在那里Kiribati的圣诞节首先到来，印度之后，非洲甚至更晚
• “全公司备忘录：我们在德里、杜塞尔多夫和底特律的每家工厂将于1月21日16:00提前一小时关闭”，每个工厂的下午4点是三个不同的时刻，每个时间间隔几个小时

TIMESTAMP WITH TIME ZONE

跟踪特定时刻、时间线上的单个点时，请使用类型为TIMESTAMP WITH TIME ZONE的列。在Postgres中，这些值存储在UTC中。与输入一起提交的任何时区或偏移量信息用于调整为UTC，然后区域/偏移量信息将被丢弃

注意：某些工具可能具有善意但不幸的反功能，即在检索UTC值后注入时区，从而误报实际存储的内容

将力矩与TIMESTAMP WITHOUT TIME ZONE的值进行比较

至于将某个时刻与TIMESTAMP WITHOUT TIME ZONE类型的列中的值进行比较，这样做通常没有意义

但是，如果您头脑清醒，受过日期时间处理方面的教育，并且在您的业务逻辑中进行这种比较是明智的，那么让我们继续前进

错班

您正在使用糟糕、糟糕、糟糕的日期时间类（Date、SimpleDateFormat，等等），这些类在几年前被java所取代。时间课程。帮自己一个忙：停止使用遗留的日期时间类。仅使用java。时间

如果以java.util.Date的形式给出一个时间，则使用添加到旧类中的新方法进行转换。特别地，java.util.Date被Instant替换

Instant instant = myJavaUtilDate.toInstant() ;  // Convert from legacy class to modern class.

指定要在其中调整以UTC为单位的Instant时刻以进行比较的时区。例如，如果您的数据库是由不了解正确的日期-时间处理的人创建的，并且一直在使用TIMESTAMP WITHOUT TIME ZONE列存储日期和取自魁北克挂钟时间的时间值，则使用时区America/Montreal

以continent/region的格式指定一个proper time zone name，例如America/Montreal、Africa/Casablanca或Pacific/Auckland。切勿使用2-4个字母的缩写，如EST或IST，因为它们不是真正的时区，不是标准化的，甚至不是唯一的（！）

ZoneId z = ZoneId.of( "America/Montreal" ) ;

将该区域应用于我们的Instant以获得一个ZonedDateTime对象

ZonedDateTime zdt = instant.atZone( z ) ;

我们得到的ZonedDateTime对象表示与Instant对象相同的时刻，时间轴上的相同点，但使用不同的挂钟时间查看

为了敲打一个square-peg into a round-hole，让我们将该ZonedDateTime对象转换为一个LocalDateTime对象，从而去除时区信息，只留下一个带有时间值的日期

LocalDateTime ldt = zdt.toLocalDateTime() ;

半开

where prc_sta_dt <= :srch_dt and prc_end_dt >= :srch_dt

这种逻辑很容易失败。通常，在定义使用半开放的时间跨度时，日期-时间处理的最佳实践是，开始是包含的，而结束是独占的

所以用这个：

WHERE instant >= start_col AND instant < stop_col ;

对于PreparedStatement，我们将使用占位符

WHERE ? >= start_col AND ? < stop_col ;

在Java方面，从JDBC4.2W开始e可以直接交换java。时间对象通过getObject和setObject方法访问数据库

根据您的JDBC驱动程序，可能能够传递Instant。JDBC规范不要求对Instant的支持。所以请尝试一下，或者阅读驱动程序的文档

myPreparedStatement.setObject( 1 , instant ) ;
myPreparedStatement.setObject( 2 , instant ) ;

如果不支持Instant，请将Instant转换为设置为UTC的OffsetDateTime。规范要求对OffsetDateTime的支持

myPreparedStatement.setObject( 1 , instant.atOffset( ZoneOffset.UTC ) ) ;
myPreparedStatement.setObject( 2 , instant.atOffset( ZoneOffset.UTC ) ) ;

检索

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

始终指定时区

For example, if the server is running in Chicago, then will it interpret 10 am as 10am CST and then convert that to 4pm UTC before doing the comparison in the database?

程序员不应该依赖于当前在主机OS或JVM上设置为默认值的时区（顺便说一下，也不应该依赖于区域设置）。这两个都是你无法控制的。而且两者都可以在运行时的任何时刻改变

始终通过将可选参数传递给各种日期时间方法来指定时区。使这些选项成为可选选项是java中的一个设计缺陷。时间在我看来，作为程序员，他们常常忽视时区的问题，这是自作自受的。但这是一个非常有用和优雅的框架中极少数的设计缺陷之一

请注意，在上面的代码中，我们指定了所需/预期的时区。主机操作系统、Postgres数据库连接和JVM的当前默认时区不会改变代码的行为

当前时刻

如果需要当前时刻，请使用以下任一选项：

• Instant.now()
  根据定义，始终使用UTC
• OffsetDateTime.now( someZoneOffset )
  从UTC的特定偏移量的挂钟时间中看到的当前时刻
• ZonedDateTime.now( someZoneId )
  生活在特定地区的人们使用的挂钟时间中的当前时刻

Java 7和Three-Ten Backport

如果您使用的是Java7，那么就没有Java。内置时间类。幸运的是，JSR 310和java的发明者。《时代周刊》的斯蒂芬·科尔伯恩（Stephen Colebourne）还领导了ThreeTen-Backport项目，开发了一个提供大部分java的库。Java 6&；的时间功能；七,

下面是一个完整的示例应用程序。java文件，显示在java 7中使用带H2 Database Engine的后端端口

在Java 7中，JDBC 4.2不可用，因此我们不能直接使用现代类。我们退回到使用^{}，它实际上代表UTC中的一个时刻，但H2将其存储到TIMESTAMP WITHOUT TIME ZONE列中，以一天中的日期和时间为原样（使用UTC的挂钟时间），同时忽略UTC方面。我没有在博士后中尝试过，但我希望你也会看到同样的行为

package com.basilbourque.example;

import java.sql.*;

import org.threeten.bp.*;

public class App {
    static final public String databaseConnectionString = "jdbc:h2:mem:localdatetime_example;DB_CLOSE_DELAY=-1";  // The `DB_CLOSE_DELAY=-1` keeps the in-memory database around for multiple connections.

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

    private void doIt () {
        System.out.println( "Bonjour tout le monde!" );

//        java.sql.Timestamp ts = DateTimeUtils.toSqlTimestamp( ZonedDateTime.of( 2018 , 1 , 23 , 12 , 30 , 0 , 0 , ZoneId.of( "America/Montreal" ) ).toLocalDateTime() );
//        System.out.println( ts );

        this.makeDatabase();

        java.util.Date d = new java.util.Date(); // Capture the current moment using terrible old date-time class that is now legacy, supplanted years ago by the class `java.time.Instant`.
        this.fetchRowsContainingMoment( d );
    }

    private void makeDatabase () {
        try {
            Class.forName( "org.h2.Driver" );
        } catch ( ClassNotFoundException e ) {
            e.printStackTrace();
        }

        try (
            Connection conn = DriverManager.getConnection( databaseConnectionString ) ;  // The `mem` means “In-Memory”, as in “Not persisted to disk”, good for a demo.
            Statement stmt = conn.createStatement() ;
        ) {
            String sql = "CREATE TABLE event_ ( \n" +
                             "  pkey_ IDENTITY NOT NULL PRIMARY KEY , \n" +
                             "  name_ VARCHAR NOT NULL , \n" +
                             "  start_ TIMESTAMP WITHOUT TIME ZONE NOT NULL , \n" +
                             "  stop_ TIMESTAMP WITHOUT TIME ZONE NOT NULL \n" +
                             ");";
            stmt.execute( sql );

            // Insert row.
            sql = "INSERT INTO event_ ( name_ , start_ , stop_ ) VALUES ( ? , ? , ? ) ;";
            try (
                PreparedStatement preparedStatement = conn.prepareStatement( sql ) ;
            ) {
                preparedStatement.setObject( 1 , "Alpha" );
                // We have to “fake it until we make it”, using a `java.sql.Timestamp` with its value in UTC while pretending it is not in a zone or offset.
                // The legacy date-time classes lack a way to represent a date with time-of-day without any time zone or offset-from-UTC.
                // The legacy classes have no counterpart to `TIMESTAMP WITHOUT TIME ZONE` in SQL, and have no counterpart to `java.time.LocalDateTime` in Java.
                preparedStatement.setObject( 2 , DateTimeUtils.toSqlTimestamp( ZonedDateTime.of( 2018 , 1 , 23 , 12 , 30 , 0 , 0 , ZoneId.of( "America/Montreal" ) ).toLocalDateTime() ) );
                preparedStatement.setObject( 3 , DateTimeUtils.toSqlTimestamp( ZonedDateTime.of( 2018 , 2 , 23 , 12 , 30 , 0 , 0 , ZoneId.of( "America/Montreal" ) ).toLocalDateTime() ) );
                preparedStatement.executeUpdate();

                preparedStatement.setString( 1 , "Beta" );
                preparedStatement.setObject( 2 , DateTimeUtils.toSqlTimestamp( ZonedDateTime.of( 2018 , 4 , 23 , 14 , 30 , 0 , 0 , ZoneId.of( "America/Montreal" ) ).toLocalDateTime() ) );
                preparedStatement.setObject( 3 , DateTimeUtils.toSqlTimestamp( ZonedDateTime.of( 2018 , 5 , 23 , 14 , 30 , 0 , 0 , ZoneId.of( "America/Montreal" ) ).toLocalDateTime() ) );
                preparedStatement.executeUpdate();

                preparedStatement.setString( 1 , "Gamma" );
                preparedStatement.setObject( 2 , DateTimeUtils.toSqlTimestamp( ZonedDateTime.of( 2018 , 11 , 23 , 16 , 30 , 0 , 0 , ZoneId.of( "America/Montreal" ) ).toLocalDateTime() ) );
                preparedStatement.setObject( 3 , DateTimeUtils.toSqlTimestamp( ZonedDateTime.of( 2018 , 12 , 23 , 16 , 30 , 0 , 0 , ZoneId.of( "America/Montreal" ) ).toLocalDateTime() ) );
                preparedStatement.executeUpdate();
            }
        } catch ( SQLException e ) {
            e.printStackTrace();
        }
    }

    private void fetchRowsContainingMoment ( java.util.Date moment ) {
        // Immediately convert the legacy class `java.util.Date` to a modern `java.time.Instant`.
        Instant instant = DateTimeUtils.toInstant( moment );
        System.out.println( "instant.toString(): " + instant );
        String sql = "SELECT * FROM event_ WHERE ? >= start_ AND ? < stop_ ORDER BY start_ ;";

        try (
            Connection conn = DriverManager.getConnection( databaseConnectionString ) ;
            PreparedStatement pstmt = conn.prepareStatement( sql ) ;
        ) {
            java.sql.Timestamp ts = DateTimeUtils.toSqlTimestamp( instant );
            pstmt.setTimestamp( 1 , ts );
            pstmt.setTimestamp( 2 , ts );

            try ( ResultSet rs = pstmt.executeQuery() ; ) {
                while ( rs.next() ) {
                    //Retrieve by column name
                    Integer pkey = rs.getInt( "pkey_" );
                    String name = rs.getString( "name_" );
                    java.sql.Timestamp start = rs.getTimestamp( "start_" );
                    java.sql.Timestamp stop = rs.getTimestamp( "stop_" );

                    // Instantiate a `Course` object for this data.
                    System.out.println( "Event pkey: " + pkey + " | name: " + name + " | start: " + start + " | stop: " + stop );
                }
            }
        } catch ( SQLException e ) {
            e.printStackTrace();
        }
    }
}

跑步的时候

instant.toString(): 2018-12-04T05:06:02.573Z

Event pkey: 3 | name: Gamma | start: 2018-11-23 16:30:00.0 | stop: 2018-12-23 16:30:00.0

Java 8，无ThreeTen后端口

这里是同一个例子，从概念上讲，但在Java8或更高版本中，我们可以使用Java。时间类内置，没有三个后端口库

package com.basilbourque.example;

import java.sql.*;

import java.time.*;

public class App {
    static final public String databaseConnectionString = "jdbc:h2:mem:localdatetime_example;DB_CLOSE_DELAY=-1";  // The `DB_CLOSE_DELAY=-1` keeps the in-memory database around for multiple connections.

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

    private void doIt ( ) {
        System.out.println( "Bonjour tout le monde!" );

        this.makeDatabase();

        java.util.Date d = new java.util.Date(); // Capture the current moment using terrible old date-time class that is now legacy, supplanted years ago by the class `java.time.Instant`.
        this.fetchRowsContainingMoment( d );
    }

    private void makeDatabase ( ) {
        try {
            Class.forName( "org.h2.Driver" );
        } catch ( ClassNotFoundException e ) {
            e.printStackTrace();
        }

        try (
                Connection conn = DriverManager.getConnection( databaseConnectionString ) ;  // The `mem` means “In-Memory”, as in “Not persisted to disk”, good for a demo.
                Statement stmt = conn.createStatement() ;
        ) {
            String sql = "CREATE TABLE event_ ( \n" +
                    "  pkey_ IDENTITY NOT NULL PRIMARY KEY , \n" +
                    "  name_ VARCHAR NOT NULL , \n" +
                    "  start_ TIMESTAMP WITHOUT TIME ZONE NOT NULL , \n" +
                    "  stop_ TIMESTAMP WITHOUT TIME ZONE NOT NULL \n" +
                    ");";
            stmt.execute( sql );

            // Insert row.
            sql = "INSERT INTO event_ ( name_ , start_ , stop_ ) VALUES ( ? , ? , ? ) ;";
            try (
                    PreparedStatement preparedStatement = conn.prepareStatement( sql ) ;
            ) {
                preparedStatement.setObject( 1 , "Alpha" );
                // We have to “fake it until we make it”, using a `java.sql.Timestamp` with its value in UTC while pretending it is not in a zone or offset.
                // The legacy date-time classes lack a way to represent a date with time-of-day without any time zone or offset-from-UTC.
                // The legacy classes have no counterpart to `TIMESTAMP WITHOUT TIME ZONE` in SQL, and have no counterpart to `java.time.LocalDateTime` in Java.
                preparedStatement.setObject( 2 , ZonedDateTime.of( 2018 , 1 , 23 , 12 , 30 , 0 , 0 , ZoneId.of( "America/Montreal" ) ).toLocalDateTime() );
                ;
                preparedStatement.setObject( 3 , ZonedDateTime.of( 2018 , 2 , 23 , 12 , 30 , 0 , 0 , ZoneId.of( "America/Montreal" ) ).toLocalDateTime() );
                preparedStatement.executeUpdate();

                preparedStatement.setString( 1 , "Beta" );
                preparedStatement.setObject( 2 , ZonedDateTime.of( 2018 , 4 , 23 , 14 , 30 , 0 , 0 , ZoneId.of( "America/Montreal" ) ).toLocalDateTime() );
                preparedStatement.setObject( 3 , ZonedDateTime.of( 2018 , 5 , 23 , 14 , 30 , 0 , 0 , ZoneId.of( "America/Montreal" ) ).toLocalDateTime() );
                preparedStatement.executeUpdate();

                preparedStatement.setString( 1 , "Gamma" );
                preparedStatement.setObject( 2 , ZonedDateTime.of( 2018 , 11 , 23 , 16 , 30 , 0 , 0 , ZoneId.of( "America/Montreal" ) ).toLocalDateTime() );
                preparedStatement.setObject( 3 , ZonedDateTime.of( 2018 , 12 , 23 , 16 , 30 , 0 , 0 , ZoneId.of( "America/Montreal" ) ).toLocalDateTime() );
                preparedStatement.executeUpdate();
            }
        } catch ( SQLException e ) {
            e.printStackTrace();
        }
    }

    private void fetchRowsContainingMoment ( java.util.Date moment ) {
        // Immediately convert the legacy class `java.util.Date` to a modern `java.time.Instant`.
        Instant instant = moment.toInstant();
        System.out.println( "instant.toString(): " + instant );
        String sql = "SELECT * FROM event_ WHERE ? >= start_ AND ? < stop_ ORDER BY start_ ;";

        try (
                Connection conn = DriverManager.getConnection( databaseConnectionString ) ;
                PreparedStatement pstmt = conn.prepareStatement( sql ) ;
        ) {
            pstmt.setObject( 1 , instant );
            pstmt.setObject( 2 , instant );

            try ( ResultSet rs = pstmt.executeQuery() ; ) {
                while ( rs.next() ) {
                    //Retrieve by column name
                    Integer pkey = rs.getInt( "pkey_" );
                    String name = rs.getString( "name_" );
                    Instant start = rs.getObject( "start_" , OffsetDateTime.class ).toInstant();
                    Instant stop = rs.getObject( "stop_" , OffsetDateTime.class ).toInstant();

                    // Instantiate a `Course` object for this data.
                    System.out.println( "Event pkey: " + pkey + " | name: " + name + " | start: " + start + " | stop: " + stop );
                }
            }
        } catch ( SQLException e ) {
            e.printStackTrace();
        }
    }
}

跑步的时候

instant.toString(): 2018-12-04T05:10:54.635Z

Event pkey: 3 | name: Gamma | start: 2018-11-24T00:30:00Z | stop: 2018-12-24T00:30:00Z

---

关于java。时间

java.time框架内置于Java8和更高版本中。这些类取代了麻烦的旧legacy日期时间类，如^{}、^{}、&^{}

现在位于maintenance mode的Joda-Time项目建议迁移到java.time类

要了解更多信息，请参阅Oracle Tutorial。并搜索堆栈溢出以获得许多示例和解释。规范是JSR 310

您可以交换java。时间对象直接与数据库连接。使用符合JDBC 4.2或更高版本的JDBC driver。不需要字符串，也不需要java.sql.*类

在哪里可以获得java。时间课

• Java SE 8、Java SE 9、Java SE 10、Java SE 11和更高版本—标准Java API的一部分，带有捆绑实现。
  • Java9添加了一些次要功能和修复
• Java SE 6和Java SE 7
  • 大部分java。时间功能被向后移植到Java6&；7英寸ThreeTen-Backport
• Android
  • java的Android捆绑包实现的更高版本。时间课程
  • 为了早期的Android（<；26）中，ThreeTenABP项目采用了ThreeTen-Backport（如上所述）。见How to use ThreeTenABP…

{a33}项目扩展了java。额外上课的时间。这个项目是将来可能添加到java的一个试验场。时间您可以在这里找到一些有用的类，例如^{}、^{}、^{}和more