On 11/9/2016 2:18 AM, Ryan Culpepper wrote:
Another option is to let PostgreSQL do it for you using AT TIME ZONE
(or the timezone function):
select ('2016-11-09 01:23:00Z'::timestamptz)
at time zone 'us/eastern';
=> 2016-11-08 20:23:00
But beware that PostgreSQL interprets numerical timezones backwards
(sometimes?) (see
https://www.postgresql.org/message-id/20151021034109.3017....@wrigleys.postgresql.org).
I've read that thread and the docs and I still can't make sense of it.
Ryan
I know about this one. The problem is there are two TZ conventions
which use opposite arithmetic.
- ISO-8601 labels east of Greenwich "+" and west "-"
- POSIX labels west of Greenwich "+" and east "-"
The IANA TZ datebase follows POSIX convention. [Or maybe the reverse: I
think the database preceded POSIX] So if you query the TZ database for
e.g., "EST", you get back "+05:00" as the offset.
Postgresql follows the ISO convention because the SQL standard follow
the ISO convention. Ask Postgresql for "+5" ... note I dropped the
separator and minutes (more below) ... and you will get back time in
Mumbai India. However, Postgresql has to interoperate with the opposing
standard TZ database, so if you ask for "EST" you will get the time for
New York City because Postgresql negated the offset.
So far, so stupid.
But, SQL defines timezones as "numeric GMT offsets --- using the ISO
sign convention". Postgresql closely follows the standard very closely
and the developers interpret that word "numeric" very literally. So if
you ask for "-05:00" - Postgresql sees a string, assumes the user wants
the POSIX meaning, and negates the offset ... giving you Mumbai.
But what about fractional hours? ISO specifies TZs by (+|-)HHMM ...
which looks like a number but is out of range for a TZ which must be
-12..+12. POSIX, however, uses a separated string "(+|-)HH:MM", which
doesn't look like a number.
In Posegresql, SESSION TIME ZONE accepts TZ names, TZ abbreviations,
POSIX offset strings, and DECIMAL offsets: e.g., -5.4, +2.71, etc. So if
you want to specify NYC more closely than -5, you can say -5.4 and
confuse people but be more accurate.
AT TIME ZONE does not accept DECIMAL offsets - it wants only a TZ name,
abbreviation, or POSIX offset string (with negated meaning).
I hope this doesn't muddy the water further. <grin>
I hate time zones.
I hate time zones.
I hate time zones.
YMMV,
George
--
You received this message because you are subscribed to the Google Groups "Racket
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
