How can I store and display dates in the timezone

Question

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.

Answer 1

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:

• Ignoring timezones altogether in Rails and PostgreSQL

About time zone names and abbreviations:

• Time zone names with identical properties yield different result when applied to timestamp

About time zone handling:

• Accounting for DST in Postgres, when selecting scheduled items
• Preserve timezone in PostgreSQL timestamptz type

Answer 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.