Search code examples
postgresqldatetimetimezonetimestamp-with-timezone

Representing a future time in PostgreSQL

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?

Solution

  • 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 |
+---------------------+