Postgres: "AT TIME ZONE 'localtime'"== "AT TIME ZONE 'utc'"?

Question

I'm struggling to understand how "AT TIME ZONE 'localtime'" exactly work? By playing with it, I found out that it acts exactly as "AT TIME ZONE 'UTC'"... But why? Is "localtime" a synonym of "UTC" in postgres? Or it comes from some setting (environment? connection timezone? although checked both, seems they are not related)...

There's "localtime" function but I think it is not involved here.

Sample SQLs:

# date
Thu Dec  8 12:00:05 AEDT 2016

# SELECT LOCALTIMESTAMP;
----------------------------
 2016-12-08 01:13:29.444725

# SELECT LOCALTIMESTAMP AT TIME ZONE 'America/New_York';
-------------------------------
 2016-12-08 06:08:31.183103+00

# SELECT LOCALTIMESTAMP AT TIME ZONE'localtime';
------------------------------
 2016-12-08 01:09:25.294063+00

# SELECT LOCALTIMESTAMP AT TIME ZONE 'utc';
 -------------------------------
 2016-12-08 01:09:44.32587+00 -- SAME AS ABOVE

 # SET TIME ZONE 'America/New_York';

 # SELECT LOCALTIMESTAMP;
 ----------------------------
  2016-12-07 20:13:34.924647

 # SELECT LOCALTIMESTAMP AT TIME ZONE 'localtime';
 ------------------------------
  2016-12-07 15:10:08.188197-05

 # SELECT LOCALTIMESTAMP AT TIME ZONE 'utc';
 ------------------------------
  2016-12-07 15:10:44.88332-05 -- SAME AS ABOVE

Any hint? Is it documented somewhere?

Solution

A timestamp in Postgres does not actually store any timezone information. Rather, this information comes from the timezone which is set by the server. Internally, all timestamp information is recorded in UTC time. So, for example, if you stored timestamp information from a timezone other than UTC, Postgres would first convert that timestamp to UTC before storing it.

From the documentation:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone.

To your actual question, localtime is just the timezone of the server which is always UTC.

Furthermore, it appears that Postgres' localtime simply wraps the C library function localtime(), which attempts to find the local system time (which is in default UTC time). Again, from the documentation:

If timezone is not specified in postgresql.conf or as a server command-line option, the server attempts to use the value of the TZ environment variable as the default time zone. If TZ is not defined or is not any of the time zone names known to PostgreSQL, the server attempts to determine the operating system's default time zone by checking the behavior of the C library function localtime(). The default time zone is selected as the closest match among PostgreSQL's known time zones. (These rules are also used to choose the default value of log_timezone, if not specified.)

Comments

[stillwaiting]

localtime is just the timezone of the server which is always UTC Thank you for the reply. However, is it documented somewhere? Can I change it and how? Just want to understand all in-s and out-s.

[Tim Biegeleisen]

@stillwaiting It appears that Postgres' locatime just wraps the C library function localtime() which obtains the local UTC system time. So it probably isn't possible to change this. If you specify a timezone in Postgres, then it will use this instead.

[karmakaze]

Note that although the timestamp type does not store timezone info, the timestamptz type does. Using timestamptz can be less error-prone than timestamp as any timestamps given can be converted correctly between timezones. This is particularly true when the timestamps are generated in code running with a different timezone than the database.