CODEWITHSUNDEEP
postgresql
Peter Zhukov
Peter Zhukov

Reputation: 339

Incorrect time zone (how to set time zone correctly)

This is PostgreSQL.

If i execute:

set timezone to '+3:00';
select '2004-10-19 10:23:54'::TIMESTAMP WITH TIME ZONE;

i get result:

2004-10-19 10:23:54-03

I've set time zone to +3, but it turned into -3. Why plus on time zone changed to minus?

If i execute

set timezone to '-3:00';
select '2004-10-19 10:23:54'::TIMESTAMP WITH TIME ZONE;

i get result:

2004-10-19 10:23:54+03

Again, minus turned into plus at time zone.

Upvotes: 1

Views: 318

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19570

From here:

https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html

The offset fields specify the hours, and optionally minutes and seconds, difference from UTC. They have the format hh[:mm[:ss]] optionally with a leading sign (+ or -). The positive sign is used for zones west of Greenwich. (Note that this is the opposite of the ISO-8601 sign convention used elsewhere in PostgreSQL.) hh can have one or two digits; mm and ss (if used) must have two.

Also:

One should be wary that it is easy to misspell a POSIX-style time zone specification, since there is no check on the reasonableness of the zone abbreviation(s). For example, SET TIMEZONE TO FOOBAR0 will work, leaving the system effectively using a rather peculiar abbreviation for UTC.

The bottom line is specifying a minimalist time zone will produce output, but it is not guaranteed to be accurate.

Upvotes: 1

Related Questions