JPA/Hibernate 的 Joda 时间排序在香港无法正常工作
我的问题是，当我使用 startTime 顺序对数据库进行查询（其中 startTime 是 Joda 时间）时，它返回从下午 4 点开始的时间。
现在，下午 4 点距离午夜 12 点正好 8 小时，巧合的是我住在香港，那是我的时区，而香港时区距离午夜 8 小时。
我的 startTime JPA 字段是：
@Index(name = "bookingStartTimeIndex")
@Column(name = "start_time")
@Type(type="org.joda.time.contrib.hibernate.PersistentLocalTimeAsTime")
private LocalTime startTime;
我使用 JPA/Hibernate，我的查询基本上是选择 * 其中：
order by b.startTime asc
现在我在此表中存储了时间从上午 9 点到下午 6 点的实体。从这个“排序依据”返回的是从下午 4 点开始到下午 6 点，然后是上午 9 点到下午 4 点的实体。 所以看起来它不知何故决定下午 4 点是午夜。
这是一个postgresql数据库，当我查看我的数据库列时，其数据类型为time_without_time_zone。
那么我怎样才能让它在上午 9 点到下午 6 点正常订购实体呢？
我发现的解决方法是更改为使用 PersistentLocalTimeExact，这适用于 我在我提到的条件下解决了下午 4 点遇到的所有问题...
@Index(name = "bookingStartTimeNumberIndex") @Column(name = "start_time_number", nullable = true) @Type(type="org.joda.time.contrib.hibernate.PersistentLocalTimeExact") 私人本地时间开始时间编号；
my problem is that when I do a query on the database with a order by startTime where the startTime is a Joda time, it is returning the times starting at 4pm.
Now, 4pm is exactly 8 hours away from 12 midnight, and coincidentally I live in Hong Kong and that is my time zone and Hong Kong timezone is 8 hours from midnight.
My startTime JPA field is:
@Index(name = "bookingStartTimeIndex")
@Column(name = "start_time")
@Type(type="org.joda.time.contrib.hibernate.PersistentLocalTimeAsTime")
private LocalTime startTime;
I use JPA/Hibernate and my query is basically to select * where:
order by b.startTime asc
Now I have entities with times from 9am to 6pm stored in this table. What comes back from this "order by" is entities starting with time 4pm going to 6pm then at 9am to 4pm.
So it looks like it has decided that 4pm is midnight somehow.
This is a postgresql database, when I look at my database colum has a datatype of time_without_time_zone.
So how can I get it to order the entities normally from 9am to 6pm?
A workaround I found is to change to use PersistentLocalTimeExact, this works for
me under the conditions I have mentioned and solves all the problems I had with 4pm...
@Index(name = "bookingStartTimeNumberIndex")
@Column(name = "start_time_number", nullable = true)
@Type(type="org.joda.time.contrib.hibernate.PersistentLocalTimeExact")
private LocalTime startTimeNumber;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这听起来像是一个数据呈现的问题，而不是一个基本问题，尽管有点不清楚。
您是说，对于数据库中时间为 0:00 的记录，当您从数据库中获取它时，它会显示为 16:00 吗？如果是这样，那么我认为从数据库读取日期存在一些混乱，并且 Joda Time 的 Hibernate 扩展中的某个位置正在创建
LocalTime实例，假设数据库时间是 UTC，然后创建的实例>LocalTime与您当地的时区，从而应用 -8 小时偏移。正如 Erwin 所说，在数据库中存储时区是更好的方法，或者如果您不能这样做，则仅存储 UTC 日期和/或时间。
我查看了 Joda Time 网站，他们推荐了 不同 Hibernate 扩展，比您使用的原始扩展得到更好的支持。
您还可以尝试在
PersistentLocalTimeAsTime类上设置断点，并查看LocalTime是如何构造的。This sounds like an issue of data presentation rather than a fundamental problem, though it is a little unclear.
Are you saying that for a record in the database that has a time of 0:00 hours it displays as 16:00 hours when you get it back from the database? If so, then I think there's some confusion over reading the date from the database and somewhere in Hibernate extension for Joda Time is creating the
LocalTimeinstance assuming the database time is UTC and then creating an instance ofLocalTimewith your local timezone, thus applying a -8 hour offset.As Erwin says, storing a timezone in the database is the better way to go, or if you can't do that then only store UTC dates and/or times.
I had a look at the Joda Time website and they are recommending a different Hibernate extension as better supported than the original one you are using.
You can also try breakpointing the
PersistentLocalTimeAsTimeclass and see how theLocalTimeis being constructed.此问题的通用解决方案是使用数据类型时间戳 而不是表中的时间。如果日期信息与您无关，您可以选择任何虚拟日期，例如 '2011-11-11 16:00'::timestamp 表示下午 4 点，'2011-11-11 09:00'::timestamp 表示下午 4 点上午 9 点
或者当然，也有办法用数据类型时间来修复您的查询……
但首先，您的初步评估可能是错误的吗？ PostgreSQL绝不会以这种方式对
没有时区的时间（或者只是默认为相同的时间）进行排序。您输入的时间“下午 4 点”正确吗？我怀疑您错误地输入了“4am”，或者您可能在排序之前将数据类型转换为文本？这个查询向您展示了什么？
The universal fix for this problem would be to use the data type timestamp instead of time in your table. If the date information is irrelevant to you, you can just pick any dummy day, like '2011-11-11 16:00'::timestamp for the 4 p.m. and '2011-11-11 09:00'::timestamp for 9 a.m.
Or course, there is ways to fix your query with data type time as well ...
But first things first, could your initial assessment be wrong? PostgreSQL would never sort a
time without time zone(or justtimewhich defaults to the same) that way. Did you enter the time "4pm" correctly? I suspect you entered "4am" by mistake, or maybe you convert the data type to text before sorting?What does this query show you?
我可以推荐您使用 Jadira Usertype （免责声明：我是 Usertype 的作者）
项目而不是 - 我相信您遇到的问题是在以下链接中讨论的问题：请参阅 http://blog.jadira.co.uk/blog/2010/5/1/javasqldate-types-and-the-offsetting-problem.html
can I recommend you use the Jadira Usertype (Disclaimer: I am the author of Usertype)
project instead - I believe the issue you have encountered is the one discussed in the following link: See http://blog.jadira.co.uk/blog/2010/5/1/javasqldate-types-and-the-offsetting-problem.html