CODEWITHSUNDEEP
javahibernatepostgresqljpajodatime
Phil
Phil

Reputation: 50486

JPA/Hibernate order by of a Joda time of the day is not working correctly in Hong Kong

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;

Upvotes: 1

Views: 1455

Answers (3)

Chris Pheby
Chris Pheby

Reputation: 323

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

Upvotes: 1

SteveD
SteveD

Reputation: 5405

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 LocalTime instance assuming the database time is UTC and then creating an instance of LocalTime with 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 PersistentLocalTimeAsTime class and see how the LocalTime is being constructed.

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658312

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 just time which 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?

SELECT startTime FROM tbl ORDER BY startTime;

Upvotes: 1

Related Questions