How do I store and display dates in a timezone as they were originally provided?
I have a server that receives data from clients in different time zones. The data feed contains people, their date of birth, and other event dates. For our purposes, it would be convenient if we could just store the dates they gave us.
For example, if a customer is in California and they tell us their date of birth is May 31st, we would like to store it in the database as May 31st 1999 Peace Time. Thus, no matter what time zone you are 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 in order to be able to find out things like “Is this person a minor” or “Was this event less than 24 hours ago?
Clients send us data via http based API. The server is written in Java (using eclipselink). The database is postgresql. Is it possible to meet these requirements?
Generally people say to store everything as UTC, but I feel it would be a bad idea because we will lose the timezone of the original data.
source to share
UTC is the way to go. For timestamptz
( timestamp with time zone
), the timezone of the input values serves only as a Postgres modifier for calculating internal UTC. (For timestamp [without time zone]
any added time zone will be ignored!). The time zone is not saved. You must save it additionally to know where in the world something happened.
If you do, you can store local timestamps as well. Just don't get confused for what it is. Either convert everything to UTC (happens automatically for timestamptz
) or convert everything to local time (specify "local": local db server local? Local to user?).
In particular, keep the exact name of the time zone (or a reference to it) rather than just "peace time". This is more accurate for daylight saving time, leap seconds, or other events.
Detailed explanation:
About the names and abbreviations of time zones:
About time zone:
source to share
Erwin Brandstetter's answer is 100% correct.
Age calculation
When it comes to issues like calculating the age of a minor, it is a little tricky due to the time of day. Using the Joda-Time library , you can call a method withTimeAtStartOfDay
to set the DateTime object to the first moment of the day. Time is usually the first point 00:00:00
, but not always due to daylight saving time or other anomalies. Ignore "midnight" classes and methods as they have been supplanted by the above method.
Also, to be accurate about age, in order to legally cover yourself, you may need to calculate age as the first moment of a day or two after your date of birth. If you do not know their time of birth and the time zone of that birth, you cannot know exactly their age.
Avoid juDate / .Calendar
The java.util.Date and .Calendar classes bundled with Java are notoriously nasty. Avoid them. Use Joda-Time and / or the new java.time package in the Java 8 bundle (inspired by Joda-Time, but redesigned).
Unlike java.util.Date, the date and time objects in both libraries know their own assigned time zone. JuDate is especially confusing because while it doesn't have an assigned timezone, its method toString
applies the JVM's current default timezone, thereby creating the illusion of an assigned timezone.
Joda-Time | java.time
Joda-Time and java.time are much clearer. You specify the timezone for each date and time object (otherwise the default JVM is assigned). You can easily convert from one time zone to another.
Both libraries use immutable objects where a new object is created based on the original rather than modifying (mutating) the original.
You can call getZone
a Joda-Time DateTime
to get its timezone name (ID) and its UTC offset for your entries if you deem important.
ISO 8601
Learn about the ISO 8601 standard for sane string formats for date and time values. Think about how to use them in the text API. ISO 8601 is now the norm for all new Internet protocols. Example: 2014-08-13T16:02:01Z
or 2014-12-22T11:54:23+04:00
.
And use the correct time zone names . Avoid three or four letter codes as they are not standardized or unique.
source to share