What is enough to store dates / times in a database from several time zones for accurate calculations?

Question

This is a HARD question. In fact, it is so complicated that the SQL standard and most of the main databases there have no idea in their implementation.

Converting all datetimes to UTC makes it easy to compare between records, but it discards information about the time zone, which means that you cannot perform calculations with them (for example, add 8 months to the stored date) and not get them in the time zone in which they were kept inside. Thus, the naive approach is absent.

Saving the time zone offset from UTC in addition to the timestamp (for example, a timestamp with a time zone in postgres) will seem sufficient, but different time intervals can have the same offset at one time in a year, and another after 6 months from for DST. For example, you could have New York and Chile as in UTC-4 now (August), but after November 4, New York will be UTC-5, and Chile (after September 2) will be UTC-3. Thus, saving only the offset will not allow you to make accurate calculations. Like the naive approach above, it also discards information.

What if you instead save the timezone identifier (e.g. America / Santiago) with a timestamp? This will allow you to distinguish between Chilean time and date and time in New York. But this is still not enough. If you keep the expiration date, say that at midnight 6 months in the future, and the DST rules change (as, unfortunately, politicians like to do), your timestamp will be incorrect and the expiration may occur at 11:00 or 1 hour instead. Which may or may not be a big problem for your application. Thus, the use of a timestamp also excludes information.

It seems that in order to really be accurate, you need to save the local time (for example, using a timestamp type other than the time zone) with the time zone identifier. To support faster comparisons, you can cache its version of utc until the db time zone you are using is updated, and then update the cached value if it has changed. Thus, it will be 2 naive types of timestamps plus the timezone identifier and some kind of external cron job that checks to see if the db time interval has changed and starts the corresponding update requests for the cached timestamp.

Is this the exact solution? Or am I still missing something? Can this be done better?

I'm interested in solutions for MySQL, SQL Server, Oracle, PostgreSQL and other DBMSs that handle TIMESTAMP WITH TIME ZONE.

Answer 1

You have described the problem well. Unfortunately, the answer is to do what you described.

The correct format used depends on the pragmatics of what the timestamp should represent. It can be generally divided between past and future events (although there are exceptions):

• Past events can and should usually be stored as something that can never be reinterpreted in different ways. (for example: UTC with a numerical time zone). If the named time zone should be saved (in order to be informative for the user), then this should be separate.

• Future events need the solution you described. Local timestamp and named time zone. This is because you want to change the "actual" (UTC) time of this event when changing time zone rules.

I would question if changing the time zone is such an overhead? This is usually pretty fast. I would survive the pain of caching if you see really significant success. There are (as you pointed out) some large operations that require caching (e.g. sorting billions of rows based on actual time (UTC).

If you need future events that will be cached in UTC for performance reasons, then yes, you need to put a process to update the cached values. Depending on the type of database, it is possible that this can be done by sysadmins, since the rules of TK rarely change.

Answer 2

If you care about the offset, you must keep the actual offset. Storing the time zone identifier is not the same as the time zone and it changes over time. By keeping the time zone offset, you can calculate the correct local time during the event, rather than the local time based on the current offset. You can still save the time zone identifier if it is important to know which real-time event was considered to be counted.

Remember that time is a physical attribute, but the time zone is political.

Answer 3

If you convert to UTC, you can order and compare entries. If you add the name of the time zone from which it arose, you can represent it in the original tz and be able to add / subtract time periods, such as weeks, months, etc. (Instead of the past tense).

In your question, you declare that this is not enough, because the DST can be changed. DST makes calculating dates (with the exception of the elapsed time) complex and rather code intensive. Just as you need a code to solve leap years, you need to consider if for a data or period you need to apply DST correction or not. For several years, the answer will be yes for others no. See this wiki page for how complicated these rules are.

Saving the bias basically saves the result of these calculations. This calculated bias is valid only for a given point in time and cannot be applied to both later and earlier points, as you suggest in your question. You perform UTC time calculation, and then convert the received time into the required time zone based on the rules that are active at this time in this time zone.

Please note that before the First World War there was not a single DST, and the date / time systems in the databases do an excellent job of these cases.

Answer 4

I'm interested in solutions for MySQL, SQL Server, Oracle, PostgreSQL and other DBMSs that handle TIMESTAMP WITH TIME ZONE.

Oracle instantly converts to UTC, but retains the time zone or UTC offset depending on what you go through. Oracle (correctly) makes the difference between timezone and UTC offset and returns what you passed to you. These are just two extra bytes.

Oracle performs all calculations on TIMESTAMP WITH TIME ZONE in UTC. It does not matter for adding months, but it does matter for adding days, since there is no daylight saving time. Note that the result of the calculation should always be a valid timestamp, for example. adding one month to January 31 will result in an exception in Oracle since February 31 does not exist.