在 PostgreSQL 的时间戳列上使用 Hibernate 进行日期查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2543510/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Date query with Hibernate on Timestamp Column in PostgreSQL
提问by Shashikant Kore
A table has timestamp column. A sample value in that could be 2010-03-30 13:42:42. With Hibernate, I am doing a range query Restrictions.between("column-name", fromDate, toDate).
表具有时间戳列。其中的示例值可能是 2010-03-30 13:42:42。使用 Hibernate，我正在执行范围查询 Restrictions.between("column-name", fromDate, toDate)。
The Hibernate mapping for this column is as follows.
此列的 Hibernate 映射如下。
<property name="orderTimestamp" column="order_timestamp" type="java.util.Date" />
Let's say, I want to find out all the records that have the date 30th March 2010 and 31st March 2010. A range query on this field is done as follows.
假设我想找出日期为 2010 年 3 月 30 日和 2010 年 3 月 31 日的所有记录。对该字段的范围查询按如下方式完成。
Date fromDate = new SimpleDateFormat("yyyy-MM-dd").parse("2010-03-30");
Date toDate = new SimpleDateFormat("yyyy-MM-dd").parse("2008-03-31");
Expression.between("orderTimestamp", fromDate, toDate);
This doesn't work.
这不起作用。
The query is converted to respective timestamps as "2010-03-30 00:00:00" and "2010-03-31 00:00:00". So, all the records for the 31st March 2010 are ignored.
查询被转换为各自的时间戳，如“2010-03-30 00:00:00”和“2010-03-31 00:00:00”。因此，将忽略 2010 年 3 月 31 日的所有记录。
A simple solution to this problem could be to have the end date as "2010-03-31 23:59:59." But, I would like to know if there is way to match only the date part of the timestamp column.
此问题的一个简单解决方案可能是将结束日期设置为“2010-03-31 23:59:59”。但是，我想知道是否有办法只匹配时间戳列的日期部分。
Also, is Expression.between()inclusive of both limits? Documentation doesn't throw any light on this.
另外，是否Expression.between()包括两个限制？文档对此没有任何说明。
回答by Pascal Thivent
, I would like to know if there is way to match only the date part of the timestamp column.
，我想知道是否有办法只匹配时间戳列的日期部分。
If you don't want a TIMESTAMP, use a DATE (and shouldn't you use type="date"?).
如果您不想要 TIMESTAMP，请使用 DATE（您不应该使用type="date"吗？）。
Also, is Expression.between() inclusive of both limits?
另外，Expression.between() 是否包括这两个限制？
It seems that the behavior of the SQL BETWEEN operator is database dependent. With PosgresQL, the BETWEEN expression:
似乎 SQL BETWEEN 运算符的行为取决于数据库。使用 PosgresQL，BETWEEN 表达式：
x BETWEEN y AND z
is semantically equivalent to:
在语义上等同于：
y <= x AND x <= z
i.e. inclusive.
即包容。
回答by Tomislav Nakic-Alfirevic
The "2010-03-31 23:59:59." border condition is potentially dangerous: if there was a transaction in the system between 23:59:59 and 00:00:00, you'd miss it. If you have enough transactions daily and a long enough period, a report is going to break and you'll spend hours to find out why.
《2010-03-31 23:59:59》。边界条件具有潜在危险：如果系统中在 23:59:59 和 00:00:00 之间有交易，您就会错过它。如果您每天有足够多的交易和足够长的时间，报告就会中断，您将花费数小时来找出原因。
PostgreSQL betweenincludes the limitsso it would not even be safe to set the end date to 2010-04-01 00:00:00. Use lt() and gt() to be sure.
PostgreSQLbetween包含限制，因此将结束日期设置为 2010-04-01 00:00:00 甚至是不安全的。使用 lt() 和 gt() 来确定。
回答by user2044365
Use cast(your_data, date), remember the second arg of the cast function must be lower, or it wont work.
使用 cast(your_data, date)，记住 cast 函数的第二个 arg 必须更低，否则将无法工作。
Att.: Samuel\Maycon
联系人：塞缪尔\梅康
回答by Frank Heikens
Maybe you can CAST orderTimestamp to a DATE. HQL does have supportfor this.
也许您可以将 orderTimestamp 转换为 DATE。HQL确实支持这一点。
