4

I have a server that is being fed data from clients in different timezones. The data feed contains people, their date of birth and other dates of events. For our purposes, it would be convenient if we could just store the dates as their given to us.

For example, if the client is in California and it tells us the person's date of birth is May 31st, we'd like to store it in the database as May 31st 1999, pacific time. This way, no matter what timezone you're in, you can see that the person was born on May 31st.

At the same time, we want to be able to query this data to be able to figure out things like, "Is this person a minor" or "did this event happen less than 24 hours ago?

The clients are sending us data over a http based rest API. The server is written in Java (using eclipselink). The database is postgresql. Is it possible to satisfy these requirements?

Typically, people say to store everything as UTC, but I feel like that would be a bad idea because we'd lose the timezone of the original data.

Daniel Kaplan
  • 62,768
  • 50
  • 234
  • 356

2 Answers2

4

UTC is the way to go. For timestamptz (timestamp with time zone) the time zone of input values only serves as modifier for Postgres to calculate UTC internally. (For timestamp [without time zone] any appended time zone would be ignored!). The time zone is not saved. You need to save it additionally to know where in the world something happened.

If you do that, you might as well store local timestamps. Just don't get confused which is which. Either convert everything to UTC (happens automatically for timestamptz), or convert everything to local time (define "local": your local? local to the db server? local to the user?).

In particular, rather store the exact time zone name (or a reference to it) than just "pacific time". This is more exact for daylight saving time, leap seconds or other events.

Detailed explanation:

About time zone names and abbreviations:

About time zone handling:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • So you're saying every date column will need an additional original_timezone column, too? – Daniel Kaplan Aug 13 '14 at 20:19
  • 1
    Yes. If you want to answer both question: `birthday of person` as well as `did this event happen less than 24 hours ago?` precisely, you need *time* and *space* ("birthday" depends on the place it happened). Actually I suggest a timestamp (type `timestamptz` or `timestamp`) rather than just a `date`. – Erwin Brandstetter Aug 13 '14 at 20:22
  • Confusingly, a `java.util.Date` stores time data. I was referring to the java date when I said that. Thanks – Daniel Kaplan Aug 13 '14 at 20:25
  • 2
    There is a lot of confusion going around related to time and time zones ... Also, I need to mention at least once, that DST (daylight saving time) is an incredibly stupid concept that should be eliminated and never again be spoken of. – Erwin Brandstetter Aug 13 '14 at 20:26
  • The answer correctly describes the best practice of storing the primary date-time vales in UTC and doing most of your business logic in UTC. Then translate to localized date-time only when expected by the user in the user-interface. Secondarily, if knowing the originally input date-time and time zone info is important, record that in addition to the primary UTC value. As the Answer states, `TIMESTAMP WITH TIME ZONE` means "with respect for time zone when inserting and retrieving the date-time value" but actually stores the date-time adjusted to UTC *without* storing original time zone info. – Basil Bourque Aug 13 '14 at 20:39
  • 1
    Carefully read [the Postgres doc](http://www.postgresql.org/docs/current/static/datatype-datetime.html) regarding date-time data types. And read this upshot summary advice, [Always Use TIMESTAMP WITH TIME ZONE](http://justatheory.com/computers/databases/postgresql/use-timestamptz.html), by Postgres expert David E. Wheeler. – Basil Bourque Aug 13 '14 at 20:43
2

The answer by Erwin Brandstetter is 100% correct.

Calculating Age

As for matters such as calculating age of a minor, that is a bit tricky because of time of day. Using the Joda-Time library, you can call the method withTimeAtStartOfDay to set a DateTime object to the first moment of the day. Usually that first moment is the time 00:00:00 but not always because of Daylight Saving Time or other anomalies. Ignore the "midnight"-related classes and methods as they have been supplanted by the above-mentioned method.

Furthermore, to be really accurate about age to cover yourself legally, you might want to calculate age as the first moment of the day or two after the birth date-time. Unless you know their time of birth and the time zone of that birth, you cannot know exactly their age.

Avoid j.u.Date/.Calendar

The java.util.Date and .Calendar classes bundled with Java are notoriously troublesome. Avoid them. Use either Joda-Time and/or the new java.time package bundled in Java 8 (inspired by Joda-Time but re-architected).

Unlike java.util.Date, the date-time objects in both the other libraries know their own assigned time zone. A j.u.Date is particularly confusing because, while it has no time zone assigned, its toString method applies the JVM’s current default time zone thereby creating the illusion of an assigned time zone.

Joda-Time | java.time

With Joda-Time and java.time, things are much clearer. You specify a time zone to each date-time object (otherwise the JVM default is assigned). You can easily convert from one time zone to other.

Both libraries use immutable objects, where a new object based on the original is created rather than changing (mutating) the original.

You can call getZone a Joda-Time DateTime object to obtain its time zone name (ID) and its offset from UTC for your records if you deem that important.

ISO 8601

Learn about the ISO 8601 standard for sensible String formats of date-time values. Consider using those in your text-based API. ISO 8601 is now the norm for all new Internet protocols. Ex: 2014-08-13T16:02:01Z or 2014-12-22T11:54:23+04:00.

And use proper time zone names. Avoid the 3 or 4 letter codes as they are neither standardized nor unique.

Community
  • 1
  • 1
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154