Ignoring time zones altogether in Rails and PostgreSQL

datetime, postgresql, ruby-on-rails, timestamp, timezone

Solution

Postgres has two different timestamp data types:

- `timestamp with time zone`, short name: `timestamptz`

- `timestamp without time zone`, short name: `timestamp`

`timestamptz` is the preferred type in the date/time family, literally. It has `typispreferred` set in `pg_type`, which can be relevant:

- Generating time series between two dates in PostgreSQL

Internal storage and epoch

Internally, timestamps occupy 8 bytes of storage on disk and in RAM. It is an integer value representing the count of microseconds from the Postgres epoch, 2000-01-01 00:00:00 UTC.

Postgres also has built-in knowledge of the commonly used UNIX time counting seconds from the UNIX epoch, 1970-01-01 00:00:00 UTC, and uses that in functions `to_timestamp(double precision)` or `EXTRACT(EPOCH FROM timestamptz)`.

The source code:

* Timestamps, as well as the h/m/s fields of intervals, are stored as
* int64 values with units of microseconds.  (Once upon a time they were  
* double values with units of seconds.)

And:

/* Julian-date equivalents of Day 0 in Unix and Postgres reckoning */  
#define UNIX_EPOCH_JDATE        2440588 /* == date2j(1970, 1, 1) */  
#define POSTGRES_EPOCH_JDATE    2451545 /* == date2j(2000, 1, 1) */  

The microsecond resolution translates to a maximum of 6 fractional digits for seconds.

`timestamp`

For `timestamp` no time zone is provided explicitly. Postgres ignores any time zone modifier added to input literals!

Nothing is shifted for display. With everything happening in the same time zone this is fine. For a different time zone the meaning changes, but value and display stay the same.

`timestamptz`

Handling of `timestamptz` is subtly different. The manual:

For `timestamp with time zone`, the internally stored value is always in UTC (Universal Coordinated Time ...)

Bold emphasis mine. The time zone itself is never stored. It is an input modifier used to compute the according UTC timestamp, which is stored. Or an output decorator according to the `timezone` setting of the current session. For input literals without offset, the current `timezone` setting is assumed. All computations are done with UTC timestamp values.

If more than one time zone may be involved, or if there can be any doubt or misunderstanding, go with `timestamptz`. Best for most use cases.

Clients like psql or pgAdmin or any application communicating via libpq (like Ruby with the `pg` gem) are presented with the offset for the current time zone or according to a given time zone (see below). It's always the same point in time, only the display format varies. As the manual puts it:

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the `TimeZone` configuration parameter before being displayed to the client.

Example in psql:

db=# SELECT timestamptz '2012-03-05 20:00+03';
      timestamptz
------------------------
 2012-03-05 18:00:00+01

What happened here? The input literal with (arbitrary) time zone offset `+03` is just another way to format the UTC timestamp `2012-03-05 17:00:00`. The result of the query is displayed for the current time zone setting, "Vienna/Austria" in my test, with an offset `+01` during winter and `+02` during summer time ("daylight saving time", DST). So `2012-03-05 18:00:00+01` for the "winter" time.

Postgres only retains the value. Just like with a decimal number: `numeric '003.4'` or `numeric '+3.4'` - either results in the exact same internal value.

`AT TIME ZONE`

To project timestamp values to a specific time zone, use the `AT TIME ZONE` construct. `timestamptz` is converted to `timestamp` and vice versa.

To get UTC `2012-03-05 17:00:00+0` as `timestamptz`:

SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC'

... which is equivalent to:

SELECT timestamptz '2012-03-05 17:00:00 UTC'

To display the same point in time as EST `timestamp` (Eastern Standard Time):

SELECT timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC' AT TIME ZONE 'EST'

That's right, `AT TIME ZONE 'UTC'` twice. The first interprets the `timestamp` value as (given) UTC timestamp returning the type `timestamptz`. The second converts `timestamptz` to `timestamp` as seen on a wall clock in the given time zone 'EST' at this point in time.

Examples

SELECT ts AT TIME ZONE 'UTC'
FROM  (
   VALUES
      (1, timestamptz '2012-03-05 17:00:00+0')
    , (2, timestamptz '2012-03-05 18:00:00+1')
    , (3, timestamptz '2012-03-05 17:00:00 UTC')
    , (4, timestamp   '2012-03-05 11:00:00' AT TIME ZONE '+6') 
    , (5, timestamp   '2012-03-05 17:00:00' AT TIME ZONE 'UTC') 
    , (6, timestamp   '2012-03-05 07:00:00' AT TIME ZONE 'US/Hawaii')  -- ①
    , (7, timestamptz '2012-03-05 07:00:00 US/Hawaii')                  -- ①
    , (8, timestamp   '2012-03-05 07:00:00' AT TIME ZONE 'HST')        -- ①
    , (9, timestamp   '2012-03-05 18:00:00+1')  -- ② loaded footgun!
      ) t(id, ts);

Returns 8 (or 9) identical rows with a `timestamptz` column representing UTC timestamp `2012-03-05 17:00:00`. The 9th row sort of happens to work in my time zone, but is an evil trap.

① Rows 6 - 8 with time zone name and time zone abbreviation for Hawaii time are subject to DST (daylight saving time) and might differ, though not for the given winter times. A time zone name like `'US/Hawaii'` is aware of DST rules and all historic shifts, while an abbreviation like `HST` is just a dumb code for a fixed offset. You may need to append a different abbreviation for summer / standard time. The name correctly adjusts any timestamp at any point in time (as recorded in the underlying library). An abbreviation is cheap, but needs to be the right one for the given timestamp:

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

Daylight Saving Time is not among the brightest ideas humanity ever came up with.

② Row 9, marked as loaded footgun happens to work for me. For `timestamp [without time zone]` input, any time zone offset is ignored! Only the bare timestamp is used. The value is then coerced to `timestamptz` in the example to match the column type. For this step, the `timezone` setting of the current session is assumed, which happens to be Europe/Vienna for me and matches `+1`. But not in other cases, resulting in a different value. In short: Don't cast `timestamptz` literals to `timestamp` or you lose the time zone offset.

Your questions

User stores a time, say March 17, 2012, 7pm. I don't want timezone conversions or the timezone to be stored.

Time zone itself is never stored. Use one of the methods above to enter a UTC timestamp.

I only use the users specified time zone to get records 'before' or 'after' the current time in the users local time zone.

You can use one query for all clients in different time zones. For absolute global time:

SELECT * FROM tbl WHERE time_col > (now() AT TIME ZONE 'UTC')::time

For time according to the local clock:

SELECT * FROM tbl WHERE time_col > now()::time

Not tired of background information, yet? There is more in the manual.

Problem

Original source

Related problems

• Generating time series between two dates in PostgreSQL
• Migrate datetime w. timezone in PostgreSQL to UTC timezone to use Django 1.4
• Time zone names with identical properties yield different result when applied to timestamp