What is proper way to persist time or date -type of information into database?

Question

What is the proper way to save date or time based data in the database? What are the proper "field mappings" for java to postgresql(or to some other database)?

That data should be stored in utc format without timezones.

-> timestamp and date based stuff fails in here, those will add current timezone (http://docs.oracle.com/javase/7/docs/api/java/util/Date.html)

-> what are the other options?

should I use "plain epoch/integer" column and other column for timezone? But then I cannot use all the functions etc. that the database is providing for me.

I could use hibernate with some jodatime magic, but in my current stack I don't have hibernate in use.

Possible solutions:

1). Change the computer/java timezone -> java will in the UCT (eg. export TZ="GMT" or -Duser.timezone=UCT)

2). Use epoch/Integer/Long values in date/time fields / types -> works but now I cannot use build in database functions.

3). Use Jodatime with custom hibernate datatypes?

4). Use Java8 new time and date apis?

Answer 1

In most cases, it is best to use the Postgres data type timestamptz (short for timestamp with time zone) when dealing with multiple time zones or when you want to save all timestamps as UTC.

Don't let the name mislead you, the time zone is not actually saved. But (as opposed to timestamp [without time zone]) the time zone from textual input is taken into account as modifier to compute the actual UTC timestamp value, which is saved.
On output, the text representation of the value is formatted according to your current time zone setting: timestamp is shifted and the according time zone modifier attached to it.

Note that timestamps without appended time zone are interpreted according to the current time zone setting of your session. If you want to enter a literal UTC value disregarding the current time zone, it has to be:

'2014-08-21 16:39:09+0'::timestamptz

not:

'2014-08-21 16:39:09'::timestamptz  -- would assume current time zone

Detailed explanation in this related answer:

• Ignoring timezones altogether in Rails and PostgreSQL

Answer 2

There is not a single correct way but I my opinion is that you should store time as a long unix timestamp and single dates as epoch days. Java 8 has nice functions to deal with them. Avoid locking yourself with jodatime and hibernate just to manage a date.

What do you mean functions that the database is providing you? You can always do SQL selects with integers and long since they are called from your program.

If you need a lot of manual use of the database (not programmatic) then you may want to use human readable dates.

Answer 3

As you say, it would be best to store dates as UTC on the database. In oracle you can use a DATE or TIMESTAMP datatype. You can then use the java layer to present your dates in local time to the user and with a java.sql.timestamp column. Joda is essentially built in to the latest version of java so definitely use that for any conversions etc. The alternative would be to store timestamp with timezone in oracle and perhaps use oracle date functions in your sql and stored procedures to convert the date as required. We do the former, but it may depend on your team (db people vs java people) and your audience - are there likely to be lots of different timezones in the user base or is timing on the DST changeover going to break your app.

If you can describe particular situations you are concerned about I'm sure someone will help out. Storing your data in UTC will at least ensure that your data is solid but may require many conversions in the presentation layer.