java - 当参数类型在 postgresql 中是 "timestamp without time zone"时，Java 中的参数类型应该是什么？

Question

我正在尝试通过 Java 和 JDBC 在 postgresql 中运行查询。

postgresql中“table”的“activitydate”列是“timestamp without time zone”。我尝试使用 String 传递日期但没有成功(org.postgresql.util.PSQLException:错误:运算符不存在:没有时区的时间戳 > 字符变化)。

我应该使用什么类型而不是 String 来存储“start_date”和“end_date”并将它们传递给查询？

顺便说一句 SELECT COUNT(*) FROM table WHERE activitydate > '2014-11-20' AND activitydate < '2014-11-21' AND zipcode = 12345在 pgadmin 中完美运行。

public Response query(
          @QueryParam("start_date") String start_date,
          @QueryParam("end_date") String end_date,
          @QueryParam("zipcode") String zipcode
          ) throws Exception {
...
...

pstmt = conn.prepareStatement("SELECT COUNT(*) FROM table WHERE activitydate > ? AND activitydate < ? AND zipcode = ?");

pstmt.setString(1, start_date); 
pstmt.setString(2, end_date); 
pstmt.setString(3, zipcode); 

更新:我将其更改为 java.sql.Timestamp 但仍然不起作用:

public Response query(
          @QueryParam("start_date") Timestamp start_date,
          @QueryParam("end_date") Timestamp end_date,
          @QueryParam("zipcode") String zipcode
          ) throws Exception {
...
...

pstmt = conn.prepareStatement("SELECT COUNT(*) FROM table WHERE activitydate > ? AND activitydate < ? AND zipcode = ?");

pstmt.setTimestamp(1, start_date); 
pstmt.setTimestamp(2, end_date); 
pstmt.setString(3, zipcode);

Answer 1

tl;dr

对于使用半开时间跨度的 SQL:
"SELECT * FROM tbl WHERE when !< ? AND when < ? ; "

myPreparedStatement.setObject(       // Use a prepared statement so you can pass smart objects rather than dumb strings.
    1 ,                              // Specify which placeholder is being fulfilled.
    LocalDate                        // Represent a date-only value, without time-of-day and without time zone or offset-from-UTC.
    .parse( "2014-11-20" )           // Parse an input string in standard ISO 8601 format to get a `LocalDate` object.
    .atStartOfDay()                  // Determine the first moment of the day on that date. Returns a `LocalDateTime` object representing a date with time-of-day but lacking any concept of time zone or offset-from-UTC.
) ;
myPreparedStatement.setObject( 
    2 , 
    LocalDate
    .parse( "2014-11-21" )
    .atStartOfDay()
) ;

注意:我猜您在数据库中使用了错误的列类型。 只能使用 TIMESTAMP WITH TIME ZONE 跟踪时刻，不能 TIMESTAMP WITHOUT TIME ZONE。搜索 Stack Overflow 了解更多信息。

一刻钟

Jens 的回答现在已经过时了。现在，您应该使用现代 java.time 类来取代麻烦的旧旧日期时间类。

“timestamp without time zone” in postgresql?

Postgres 和 SQL 标准上的这种数据类型有意缺少与 UTC 或时区的偏移量的上下文。所以，请注意，此类型不能代表一个时刻，不是时间轴上的一个点。

I changed it to java.sql.Timestamp

不，错误的数据类型。 java.sql.Timestamp 类除了是遗留的，而且在设计上存在严重缺陷之外，还代表了一个时刻，时间轴上的一个特定点。所以这与您的 TIMESTAMP WITHOUT TIME ZONE 类型的列不匹配。

LocalDateTime

您应该改为使用 LocalDateTime 类。此类表示具有一天中的时间的日期，但缺少任何偏移量或时区的概念。

从数据库中检索值。

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

向数据库发送一个值。

myPreparedStatement.setObject( … , ldt ) ;

一天的开始

SELECT COUNT(*) FROM table WHERE activitydate > '2014-11-20' AND activitydate < '2014-11-21'

这里还有另一个不匹配。您提供的是仅限日期的值，但您的列包含日期和时间值。

另一个问题:您在应该使用智能对象的地方使用了哑字符串。从 JDBC 4.2 开始，我们可以与数据库交换 java.time 对象。使用带占位符的 PreparedStatement，并通过 `set

传递对象

要解决日期和时间问题，我们需要确定一天的开始。使用 LocalDateTime ，我们无需考虑时区异常。所以一天总是从 00:00 开始。尽管如此，我们应该养成使用 java.time 来确定一天开始时间的习惯。

LocalDate startDate = LocalDate.parse( "2014-11-20" ) ;
LocalDate stopDate = LocalDate.parse( "2014-11-21" ) ;

LocalDateTime start = startDate.atStartOfDay() ;
LocalDateTime stop = stopDate.atStartOfDay() ;

用占位符编写 SQL。

我建议您改变想法，养成使用半开放方法定义时间跨度的习惯。在 Half-Open 中，开始是包容性，而结束是排他性。因此，对于 20 日这一整天，搜索等于或晚于 20 日，并一直到但不包括 21 日的日期。至于第一部分，“等于或晚于”的更短表达方式是“不早于”，因此我们使用 !< 。

String sql = "SELECT * FROM tbl WHERE when !< ? AND when < ? ; " ;

将该 sql 字符串提供给您准备好的语句。然后为占位符传递两个 LocalDateTime 对象。

myPreparedStatement.setObject( 1 , start ) ;
myPreparedStatement.setObject( 2 , stop ) ;