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?

2 Answers 11

up vote 276 down vote accepted

The data type timestamp is the short name for timestamp without time zone.
The other option timestamptz is short for timestamp with time zone.

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

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 is fine. If the time zone setting for the session changes, so does the meaning of the timestamp - the value stays the same.

timestamptz

Handling of 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 an input modifier used to compute the according UTC timestamp, which is stored - or and output modifier used to compute the local time to display - with appended time zone offset. If you don't append an offset for timestamptz on input, the current time zone setting 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 one time zone, use timestamptz.

Clients like psql or pgAdmin or any application communicating via libpq (like Ruby with the pg gem) are presented with the timestamp plus offset for the current time zone or according to a requested time zone (see below). It is always the same point in time, only the display format varies. Or, 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.

Consider this simple example (in psql):

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

Bold emphasis mine. What happened here?
I chose an arbitrary time zone offset +3 for the input literal. 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 for the current time zone setting Vienna/Austria in my test, which has an offset +1 during winter and +2 during summer time: 2012-03-05 18:00:00+01, because it falls into winter time.

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. numeric '003.4', numeric '3.40' or numeric '+3.4' - 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 timestamp '2012-03-05 17:00:00' AT TIME ZONE 'UTC'

To display the same timestamp value as UTC timestamp:

SELECT timestamp '2012-03-05 17:00:00' 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
      (timestamptz '2012-03-05 17:00:00+0')
    , (timestamptz '2012-03-05 18:00:00+1')
    , (timestamp   '2012-03-05 18:00:00+1')  -- ① loaded footgun!
    , (timestamp   '2012-03-05 11:00:00'  AT TIME ZONE '+6') 
    , (timestamp   '2012-03-05 17:00:00'  AT TIME ZONE 'UTC') 
    , (timestamp   '2012-03-05 07:00:00'  AT TIME ZONE 'US/Hawaii')  -- ②
    , (timestamp   '2012-03-05 07:00:00'  AT TIME ZONE 'HST')  -- ②
      ) t(ts);

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

① 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.

② 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.

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.

7 upvote
  flag
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
2 upvote
  flag
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
2 upvote
  flag
@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
4 upvote
  flag
@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
1 upvote
  flag
@BasilBourque: I am aware of this unfortunate mistake. If you don't mind, you are very welcome to edit it. I have seen some of your answers in the past and you are good at it. One more edit from me would force this to community wiki - over time I have put in a lot of effort (and edits) to make it clear and comprehensive. – Erwin Brandstetter
2 upvote
  flag
CORRECTION: On my earlier comment, I incorrectly cited the Postgres epoch as 2001. Actually it is 2000. – Basil Bourque
upvote
  flag
@BasilBourque: You may be interested in this related, recent change of policy. The counter-intuitive force-conversion to community wiki is history now. – Erwin Brandstetter
upvote
  flag
Is there a way to have the AT TIME ZONE statement for SELECT p.* like queries when the timestamp column is one of the columns of the table p. //allinonescript.com/questions/39211953/… – Işık

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.

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