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:
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.
If you define a data type
[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.
timestamp with time zone is subtly different. I quote the manual here:
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
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';
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 '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
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'
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].
SELECT ts AT TIME ZONE 'UTC'
(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') -- ②
Returns 7 (6) identical rows of the same UTC timestamp
① 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.
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.