Representing a future time in PostgreSQL

Question

I’ve been conditioned to store past dates as UTC in a database since that is in fact when the event occurred. For future dates, I would store it with a specific timezone, to avoid changes such as leap seconds or timezone rule changes.

Postgres has timestamp with timezone, but under the covers, it stores it as UTC, inferring that the specified timezone is an offset of UTC. If the timezone rules were to change, that would not be reflected in the column.

What is recommended in this case?

Answer 1

For future dates, I would store it with a specific timezone, to avoid changes such as leap seconds or timezone rule changes.

That seems backward. The main advantage of UTC over other time zones is that it is less prone to unexpected future changes: UTC only introduces leap seconds at known, limited points of the calendar year; and has none of the frequent politically-mandated offset changes.

Storing values in some locally-managed timezone leaves those values more prone (compared with UTC) to arbitrary, unpredictable future changes in meaning.

So, the general recommendation is: Store all time values (whether date, or date+time) as UTC in the database, process them internally as UTC values; and convert to/from a local timezone only at external interfaces.

For PostgreSQL, that means prefer TIMESTAMP WITH TIME ZONE.

Answer 2

Think [of it] like a calendar event. UTC doesn’t make sense for that

It sounds like you want to store a localtime with respect to a certain timezone. In that case, store a timestamp (without timezone) and the timezone in a separate column.

For example, suppose you want to record an event which will occur at 10 am on Feb 26, 2030 in Chicago and it must be at 10 am localtime regardless of the timezone rule in effect on that date.

If the database stores the timestamp without timezone:

unutbu=# select '2030-02-26 10:00:00'::timestamp as localtime, 'America/Chicago' AS tzone;
+---------------------+-----------------+
|      localtime      |      tzone      |
+---------------------+-----------------+
| 2030-02-26 10:00:00 | America/Chicago |
+---------------------+-----------------+

Then later, you can find the UTC datetime of the event using

unutbu=# select '2030-02-26 10:00:00'::timestamp AT TIME ZONE 'America/Chicago' AT TIME ZONE 'UTC';
+---------------------+
|      timezone       |
+---------------------+
| 2030-02-26 16:00:00 |
+---------------------+

The query returns the UTC datetime, 2030-02-26 16:00:00, which corresponds to 2030-02-26 10:00:00 localtime in Chicago.

Using AT TIME ZONE delays the application of the timezone rules to when the query is made instead of when the timestamptz was inserted.

---

Using AT TIME ZONE on a timestamp localizes the datetime to the given timezone, but reports the datetime in the user's timezone. Using AT TIME ZONE on a timestamptz converts the datetime to the given timezone, then drops the offset, thus returning a timestamp. Above, AT TIME ZONE is used twice: first to localize a timestamp and next to convert the returned timestamptz to a new timezone (UTC). The result is a timestamp in UTC.

Here is an example, demonstrating AT TIME ZONE's behavior on timestamps:

unutbu=# SET timezone = 'America/Chicago';
unutbu=# SELECT '2030-02-26 10:00:00'::timestamp AT TIME ZONE 'America/Chicago';
+------------------------+
|        timezone        |
+------------------------+
| 2030-02-26 10:00:00-06 |
+------------------------+

unutbu=# SET timezone = 'America/Los_Angeles';
unutbu=# SELECT '2030-02-26 10:00:00'::timestamp AT TIME ZONE 'America/Chicago';
+------------------------+
|        timezone        |
+------------------------+
| 2030-02-26 08:00:00-08 |
+------------------------+

2030-02-26 10:00:00-06 and 2030-02-26 08:00:00-08 are the same datetimes but reported in different user timezones. This shows 10am in Chicago is 8am in Los Angeles (using current timezone definitions):

unutbu=# SELECT '2030-02-26 10:00:00-06'::timestamptz AT TIME ZONE 'America/Los_Angeles';
+---------------------+
|      timezone       |
+---------------------+
| 2030-02-26 08:00:00 |
+---------------------+

---

An alternative to using AT TIME ZONE twice is to set the user timezone to UTC. Then you could use

select localtime AT TIME ZONE tzone

Note that when done this way, a timestamptz is returned instead of a timestamp.

---

Beware that storing localtimes can be problematic because there can be nonexistent times and ambiguous times. For example, 2018-03-11 02:30:00 is a nonexistent localtime in America/Chicago. Postgresql normalizes nonexistent localtimes by assuming it refers to the corresponding time after Daylight Savings Time (DST) has begun (as though someone forgot to set their clock forward):

unutbu=# select '2018-03-11 02:30:00'::timestamp AT TIME ZONE 'America/Chicago' AT TIME ZONE 'UTC';
+---------------------+
|      timezone       |
+---------------------+
| 2018-03-11 08:30:00 |
+---------------------+
(1 row)

unutbu=# select '2018-03-11 03:30:00'::timestamp AT TIME ZONE 'America/Chicago' AT TIME ZONE 'UTC';
+---------------------+
|      timezone       |
+---------------------+
| 2018-03-11 08:30:00 |
+---------------------+
(1 row)

An example of an ambiguous localtime is 2018-11-04 01:00:00 in America/Chicago. It occurs twice due to DST. Postgresql resolves this ambiguity by choosing the later time, after DST has ended:

unutbu=# select '2018-11-04 01:00:00'::timestamp AT TIME ZONE 'America/Chicago' AT TIME ZONE 'UTC';
+---------------------+
|      timezone       |
+---------------------+
| 2018-11-04 07:00:00 |
+---------------------+

Notice that this means there is no way to refer to 2018-11-04 06:00:00 UTC by storing localtimes in the America/Chicago timezone:

unutbu=# select '2018-11-04 00:59:59'::timestamp AT TIME ZONE 'America/Chicago' AT TIME ZONE 'UTC';
+---------------------+
|      timezone       |
+---------------------+
| 2018-11-04 05:59:59 |
+---------------------+

Answer 3

Particularly if you want to protect yourself from future time zune changes you should use timestamp with time zone.

PostgreSQL internally stores the number of microseconds since 2000-01-01 00:00:00, which is safe from time zone changes. If you keep updating your PostgreSQL, it will always display that absolute value correctly for your session time zone.

There is no provision for leap seconds in PostgreSQL.