PostgreSQL output timestamp with time zone

Question

Trying to output a timestamp with time zone in the PostgreSQL.
I have tried several params, the first two are fine.

(1):

select time at time zone 'UTC' from sf where id='365498691097858048';

it outputs:

2013-08-08 15:44:13

(2):

select time at time zone 'PDT' from sf where id='365498691097858048';

it outputs

2013-08-08 08:44:13

But the third one confuses me:

(3):

select time at time zone 'UTC-07' from sf where id='365498691097858048';

2013-08-08 22:44:13

'UTC-07' is the same as 'PDT', why the outputs are totally different?

And time is supposed to be the name for a column holding .. what? a timestamp or a timestamp with time zone? I can't find your table definition. Either way, don't use the reserved word time as column name - even less for a column of different data type. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 6, 2014 at 21:59
Maybe PostgreSQL is seeing UTC-07, parsing as far as UTC and going "I know that time zone", ignoring the -07 suffix. I can't reproduce it here on my 9.3 install if so - what PostgreSQL version are you running? (Always include your PostgreSQL version in questions). — Craig Ringer
– Craig Ringer, Commented Jan 7, 2014 at 0:50

Moshe Katz · Accepted Answer · 2018-05-09 19:27:54Z

This happens because PostgreSQL is being compliant with the POSIX standard when you use timezones without names.

From the IANA Timezone database:

# Be consistent with POSIX TZ settings in the Zone names,
# even though this is the opposite of what many people expect.
# POSIX has positive signs west of Greenwich, but many people expect
# positive signs east of Greenwich.  For example, TZ='Etc/GMT+4' uses
# the abbreviation "-04" and corresponds to 4 hours behind UT
# (i.e. west of Greenwich) even though many people would expect it to
# mean 4 hours ahead of UT (i.e. east of Greenwich).

From the POSIX Spec as documented for GNU libc (emphasis mine):

The offset specifies the time value you must add to the local time to get a Coordinated Universal Time value. It has syntax like [+|-]hh[:mm[:ss]]. This is positive if the local time zone is west of the Prime Meridian and negative if it is east. The hour must be between 0 and 24, and the minute and seconds between 0 and 59.

Collectives™ on Stack Overflow

PostgreSQL output timestamp with time zone

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related