up vote 114 down vote favorite
44

I'm dealing with dates and times in Rails and Postgres and running into this issue:

The database is in UTC.

The user sets a time-zone of choice in the Rails app, but it's only to be used when getting the users local time for comparing times.

User stores a time, say March 17, 2012, 7pm. I don't want timezone conversions or the timezone to be stored. I just want that date and time saved. That way if the user changed their time zone, It would still show March 17, 2012, 7pm.

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

I'm currently using 'timestamp without time zone' but when I retrieve the records, rails (?) converts them to the time zone in the app, which I don't want. 

Appointment.first.time
 => Fri, 02 Mar 2012 19:00:00 UTC +00:00

Because the records in the database seem to come out as UTC, my hack is to take the current time, remove the time zone with 'Date.strptime(str, "%m/%d/%Y")' and then do my query with that:

.where("time >= ?", date_start)

It seems like there must be an easier way to just ignore time zones all around. Any ideas?

share|improve this question
up vote 249 down vote accepted

The data type timestamp in PostgreSQL is timestamp without time zone by default.
The other option is timestamptz (timestamp with time zone).

Epoch

Internally, timestamps are stored as a count from an epoch. Postgres uses an epoch of the first moment of the first day of the year 2000 in UTC, that is, 2000-01-01T00:00:00Z. Eight octets are used to store the count number. Depending on a compile time option, that number is either:

  • An 8-byte integer (default), with 0 to 6 digits of a fractional second
  • A floating point number (deprecated), with 0 to 10 digits of a fractional second, where precision quickly degrades for values further away from epoch.
    Modern Postgres installations use the 8-byte integer.

Note that Postgres does not use Unix time. Postgres’ epoch is first moment of 2000-01-01 rather than Unix’ 1970-01-01. While Unix time has a resolution of whole seconds, Postgres keeps fractions of seconds.

timestamp

If you define a data type timestamp [without time zone] you are telling Postgres: "I am not providing a time zone explicitly, assume the current time zone instead. Postgres saves the timestamp as is - ignoring a time zone modifier if you should add one!

When you later display that timestamp, you get back what you entered literally. With the same time zone setting, all if fine. If the time zone setting for the session changes, so does the meaning of the timestamp - the value stays the same.

timestamptz

Handling for timestamp with time zone is subtly different. I quote the manual here:

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 used to compute the according UTC timestamp, which is stored. If you don't append a time zone modifier for a timestamptz on input, the local time zone of the session is assumed. All computations are done with UTC timestamp values. If you have to (or may have to) deal with more than on time zone, you should use timestamptz.

Clients like psql or pgAdmin (or Ruby) are presented with the UTC timestamp value according to the current time zone (timestamp or timestamp with time zone alike) or according to a requested time zone (see below). It is always the same point in time, only the display format varies. Or, how 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.

Consider this simple example (in psql):

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

Bold emphasis mine. What happened here?
I chose an arbitrary time zone offset +3. To Postgres, this is just one of many ways to input the UTC timestamp 2012-03-05 17:00:00. The result of the query is displayed in the format for the current time zone: 2012-03-05 18:00:00+01. (My server in Vienna/Austria is configured with the according time zone +1.)

Postgres has already forgotten how this value has been entered. All it remembers is the value and the data type. Just like with a decimal number. '03.4'::numeric, '3.40'::numeric or '3.4'::numeric - all result in the exact same internal value.

AT TIME ZONE

As soon as you get a grasp on this logic, you can do anything you want. All that's missing now, is a tool to interpret or represent timestamp literals according to a specific time zone. That's where the AT TIME ZONE construct comes in. Be aware of two different use cases. timestamptz is converted to timestamp and vice versa.

To enter the UTC timestamp 2012-03-05 17:00:00:

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

To display the same timestamp value as UTC timestamp:

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

That's right, AT TIME ZONE 'UTC' twice. The first one interprets the timestamp literal as UTC timestamp and outputs the type timestamptz. The second one converts it back to timestamp [without time zone].

Examples

SELECT ts AT TIME ZONE 'UTC'
FROM  (VALUES
      ('2012-03-05 17:00:00+0')
     ,('2012-03-05 18:00:00+1'::timestamp with time zone)
     ,('2012-03-05 18:00:00+1'::timestamp) --1loaded footgun!
     ,('2012-03-05 11:00:00'::timestamp AT TIME ZONE '+6') 
     ,('2012-03-05 17:00:00'::timestamp AT TIME ZONE 'UTC') 
     ,('2012-03-05 10:00:00'::timestamp AT TIME ZONE 'US/Hawaii') --2
     ,( TIMESTAMP '2012-03-05 07:00:00' AT TIME ZONE 'HST') --2
      ) AS v(ts);

Returns 7 (6) identical rows of the same UTC timestamp 2012-03-05 17:00:00.

1 The third row, marked as loaded footgun works for me, but only by coincidence. If you explicitly cast a timestamp literal to timestamp [without time zone], the time zone offset will be ignored! It is interpreted according to the local time zone instead, which happens to be the same time zone +1 in my case. But probably not for you - which will result in a different value.

2 Note that the last two rows with time zone name and time zone abbreviation for Hawaii time are subject to DST and may differ. A time zone name like 'US/Hawaii' is aware of DST (daylight saving time) rules and all other (historic) shifts automatically, while an abbreviation like HST is just a dumb code for a fixed offset. You may need to append a different abbreviation for summer / winter time. The name correctly interprets any timestamp at the given time zone. An abbreviation is cheap, but needs to be the right one for the given year and time of year:

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

Because your database is in UTC, AT TIME ZONE 'UTC' is the default and can be dropped locally.

As to 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.

And:

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 on date/time handling, yet? There is more in the manual.

share|improve this answer
6  
Wow, excellent and thorough answer, even though I need to read through it a couple more times to really grasp it all! Thank you! – 99miles Mar 6 '12 at 6:28
1  
Minor detail, but I think timestamps are stored internally as the number of microseconds since 2000-01-01 - see date/time datatype section of the manual. My own inspections of the source seem to confirm it. Strange to use a different origin for the epoch! – harmic Nov 29 '13 at 2:57
2  
@harmic As for different epoch… Actually not so strange. This Wikipedia page lists two dozen epochs used by various computer systems. While the Unix epoch is common, it is not the only one. – Basil Bourque Feb 15 '14 at 10:04
4  
@ErwinBrandstetter This is a great answer, except for one serious flaw. As harmic commented, Postgres does not use Unix time. According to the doc: (a) The epoch is 2001-01-01 rather than Unix’ 1970-01-01, and (b) While Unix time has a resolution of whole seconds, Postgres keeps fractions of seconds. The number of fractional digits depends on the compile time option: 0 to 6 when eight-byte integer storage (default) is used, or from 0 to 10 when floating-point storage (deprecated) is used. – Basil Bourque Feb 15 '14 at 10:16
2  
CORRECTION: On my earlier comment, I incorrectly cited the Postgres epoch as 2001. Actually it is 2000. – Basil Bourque Feb 16 '14 at 3:35
up vote 0 down vote

If you want to deal in UTC by default:

In config/application.rb, add:

config.time_zone = 'UTC'

Then, if you store the current user timezone name is current_user.timezone you can say.

post.created_at.in_time_zone(current_user.timezone)

current_user.timezone should be a valid timezone name, otherwise you will get ArgumentError: Invalid Timezone, see full list.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.