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?
Best Answer
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 hastypispreferred
set inpg_type
, which can be relevant: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)
orEXTRACT(EPOCH FROM timestamptz)
.The source code:
And:
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 the input literal by mistake!No hours are 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. I quote the manual here: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 decorator used to compute the local time for 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 (may) have to deal with more than one time zone, usetimestamptz
. In other words: If there can be any doubt or misunderstanding about the assumed time zone, go withtimestamptz
. Applies in most use cases.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:
Example in psql:
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 timestamp2012-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 ("daylight saving time", DST). So2012-03-05 18:00:00+01
as DST only kicks in later.Postgres forgets the input literal immediately. All it remembers is the value for the data type. Just like with a decimal number.
numeric '003.4'
ornumeric '+3.4'
- both result in the exact same internal value.AT TIME ZONE
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. There are two different use cases.timestamptz
is converted totimestamp
and vice versa.To enter the UTC
timestamptz
2012-03-05 17:00:00+0
:... which is equivalent to:
To display the same point in time as EST
timestamp
(Eastern Standard Time):That's right,
AT TIME ZONE 'UTC'
twice. The first interprets thetimestamp
value as (given) UTC timestamp returning the typetimestamptz
. The second converts thetimestamptz
to thetimestamp
in the given time zone 'EST' - what a wallclock displays in the time zone EST at this point in time.Examples
Returns 8 (or 9) identical rows with a timestamptz columns holding the same 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. See below.① 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 currently. A time zone name like
'US/Hawaii'
is aware of DST rules and all historic shifts automatically, while an abbreviation likeHST
is just a dumb code for a fixed offset. You may need to append a different abbreviation for summer / standard 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 timestamp:Daylight Saving Time is not among the brightest ideas humanity ever came up with.
② Row 9, marked as loaded footgun works for me, but only by coincidence. If you explicitly cast a literal to
timestamp [without time zone]
, any time zone offset is ignored! Only the bare timestamp is used. The value is then automatically coerced totimestamptz
in the example to match the column type. For this step, thetimezone
setting of the current session is assumed, which happens to be the same time zone+1
in my case (Europe/Vienna). But probably not in your case - which will result in a different value. In short: Don't casttimestamptz
literals totimestamp
or you lose the time zone offset.Your questions
Time zone itself is never stored. Use one of the methods above to enter a UTC timestamp.
You can use one query for all clients in different time zones.
For absolute global time:
For time according to the local clock:
Not tired of background information, yet? There is more in the manual.