On Thu, Nov 10, 2016 at 4:52 AM, George Neuner <gneun...@comcast.net> wrote:
>
> 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/mes
>> sage-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]
It's the reverse.
> So if you query the TZ database for e.g., "EST", you get back "+05:00" as
> the offset.
>
It's -05:00, but a quick word on the use of "EST," in particular:
The string "EST" is used in two ways in the tz database; it's the name of a
time zone that consistently has a UTC offset of -05:00, and it's also the
symbolic name for the UTC offset of -05:00. These are different things. In
the tz database, "time zone" doesn't mean the same thing as "UTC offset." A
time zone is a collection of rules for determining what the UTC offset
should be at a particular point in time in a particular region.
For example, America/New_York is the canonical name of the time zone that's
used on the Eastern seaboard of the United States. This time zone also has
at least one synonym in the database (viz., US/Eastern). In this time zone,
any point in time will occur at one of several UTC offsets. On my machine,
using the system native zoneinfo database, I have the following UTC offsets
that are used by America/New_York:
(vector
(tzoffset -14400 #t "EDT")
(tzoffset -18000 #f "EST")
(tzoffset -14400 #t "EWT")
(tzoffset -14400 #t "EPT"))
("EWP" and "EPT," by the way, stand for "Eastern War Time" and "Eastern
Peace Time," respectively.)
(Note that the *time zone* called "EST" is not a synonym for
America/New_York, because the former maps every point in time to UTC-05:00,
whereas the latter does not.)
Oh, and OS X (for... uh, reasons) uses the 32-bit version of the zoneinfo
database. This is only capable of mapping 32-bit POSIX timestamps to UTC
offsets. If you had the 64-bit version instead, there would be an
additional offset in this timezone, namely "LMT."
So, getting back to what I was saying: "EST" is both the name of a *time
zone* that has only one UTC offset (which is also named "EST"), and it's
also the name of a UTC offset.
Now, the symbolic names of UTC offsets are not unique across the tz
database. "EST" (when used as the name of an offset as opposed to the name
of a time zone) is not guaranteed to correspond to -05:00 -- though I
think, at the moment, it might. A better example is "CST." If you live in
the US, you probably think, "Oh, that stands for "Central Standard Time,"
which is UTC-06:00." And it does, but it's also the name of an offset used
in the Asia/Taipei time zone, where it corresponds to UTC+8:00.
What's the point of all of this? If you use the Racket function
`current-date` and you happen to live on the Eastern seaboard of the US,
you might see in the time-zone-name field of the date* struct the string
"EST." That is *not* the name of a time zone in the IANA sense; it's the
name of a UTC offset. And since these names do not uniquely identify UTC
offsets, they're only there as convenient, human-readable abbreviations.
They are not useful for doing work.
In summary:
- Do not use the time-zone-name field of the date* struct for any
computation. Use time-zone-offset instead.
- If you need real time zones, in the IANA sense -- that is, if you don't
already know the local UTC offset of some absolute point in time -- use
Gregor.
>
> 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.
>
I thought Postgres used the same convention as IANA, but it's been a while.
>
> 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.
>
Civil time is messy, no question.
- Jon
--
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.
