1

For event based columns in my database like createdDate I want to store a UTC value.

I am looking for advice if what I am currently doing will solve my problem correctly.

  1. In postgresql, I will use a timestamp without timezone column

  2. In my scala (similiar to java) code, I will generate a timestamp like:

    val currentMs: Long = System.currentTimeMillis
new java.sql.Timestamp(currentMs)

  3. I will save that value to the database. All my servers time will be in synch. Now at the database level, I am storing a pure UTC value.

  4. When I retrieve data from the database, I will use Joda-Time which will take the timestamp value from the database and then convert the value to a particular timezone.

Is this the correct way of doing UTC? Am I missing something here because this topic is confusing when you google about it :)

Note: This is a brand new project so I can change things around without issue.

Update

In Step#2, instead of storing the value from a java.sql.Timestamp, what if I just stored the raw currentMs value in a long column. Then in the UI I would convert the unix time to a particular timezone based on the logged in user.

Is this a suitable?

Blankman
  • 259,732
  • 324
  • 769
  • 1,199
  • 2
    I know nothing of Scala or this Jodatime you mention. However, for PostgreSQL use "timestamp with time zone" and supply the time-zone when storing a timestamp. That will store an absolute timestamp. Then, on retrieval just specify your client timezone (or use `AT TIME ZONE t`). No idea how all this will work with your framework layers. – Richard Huxton May 22 '14 at 15:26
  • @RichardHuxton why would I specify a timezone in postgresql, don't I just need the raw utc/unix time? I don't understand that. – Blankman May 22 '14 at 18:54
  • `timestamp without time zone` is *not* the same as UTC time. It a value that is interpreted as "local time". The meaning depends on (and changes with) your current time zone setting. This related answer may be of help: http://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/9576170#9576170 – Erwin Brandstetter May 22 '14 at 19:54
  • @RichardHuxton JodaTime's going to become quite important - it's the foundation of the new date/time APIs finally being introduced into Java to replace the archaic horror of `java.util.Date` and the not-much-better `Calendar` class. – Craig Ringer May 23 '14 at 10:36
  • I strongly recommend using `timestamp with time zone` in the DB, especially as you're storing UTC instants. Otherwise that sounds OK. – Craig Ringer May 23 '14 at 10:39

3 Answers3

1

Possible duplicate of What is the most recommended way to store time in PostgreSQL using Java?

Note that postgresql doesn't actually store the timezone, regardless of the datatype name.

Also see my blog post at http://greybeardedgeek.net/2012/11/24/java-dates/

Community
  • 1
  • 1
GreyBeardedGeek
  • 29,460
  • 2
  • 47
  • 67
1

(a) Search StackOverflow as this has been asked and answered many times.

(b) Regarding your item # 1: NO, do not use TIMESTAMP WITHOUT TIME ZONE. That data type means Postgres will ignore any specified time zone.

The Postgres expert David E Wheeler advises to always use TIMESTAMP WITH TIME ZONE (with one rare exception).

Read the doc carefully; do not assume how the data types work. The "with/without time zone" names are misnomers, as Postgres timestamps never have time zone information (they are framed in terms of UTC). The difference between "with/without time zone" is whether time zones are respected/applied as data is inserted/selected.

Experiment a little to clarify your understanding.

(c) Yes, it is a good idea generally to store your date-time values in UTC while translating to local time (time zone) only for presentation.

(d) Postgres has functions to capture the current date-time. You can call those functions rather than doing so in your Java/Scala code, where appropriate.

(e) Not sure what you meant by second sentence of item # 3. If you mean clocks on the computers, it is almost always best to set servers’ time zone to UTC (or Reykjavík Iceland). But your database and programming should never depend on that.

Postgres has good support for date-time work (among the best of any database), but read the doc carefully so you understand the nuances.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • What I am confused about is, why can't I just store the unix time/utc in the database in a long field, and then when I present the time in my UI just convert that based on the currently logged in users timezone? – Blankman May 26 '14 at 17:39
  • @Blankman For the same reason you don't handle your text as an array of octets, and doing the Latin-1, UTF-8, and UTF-16 translations yourself. You *can*, but I bet you don't. You use the textual data types in your programming language/libraries, database fields, and database drivers to assist you with the nitty-gritty chores of handling text. So do the same with date-time. Handling date-times directly as long integers makes it difficult for you to verify the values, leading to errors and confusion. – Basil Bourque May 26 '14 at 20:00
  • 2
    Immediate first issue with roll-your-own date-time as longs: The old java.util.Date classes, and Joda-Time, track time as *milliseconds* since a Unix epoch. The new java.time package in Java 8 tracks *nanoseconds* since Unix epoch. Older date-time utilities track *whole seconds* since epoch. And if you do run into any Postgres TIMESTAMP values, those are fractional seconds from a different epoch, 2000-01-01. Did you know there are [many more epochs](http://en.wikipedia.org/wiki/Epoch_%28reference_date%29) used by various systems and libraries? Date-time is messy work; get all the help you can. – Basil Bourque May 26 '14 at 20:07
  • Interesting, I appreciate you shedding some light on this. My head hurts already :) – Blankman May 26 '14 at 21:33
0

Yes, saving millis since the epoch in the DB and only converting them to timezones locally is a good approach. This also allows you to forget about date/time keeping DB types and just store the millis as a number. It's my preferred approach as well and the cool thing about it: it works across multiple types of DBs.

Sandman
  • 2,577
  • 2
  • 21
  • 32