Odd timezone backend output

Andrew Chernow <ac@esilo.com> writes:

I am confused about the below results. The backend is in EDT but it is
converting timestamps into EST ... excluding NOW(). Regardless of the
timezone provided, the backend is dishing out EST.

Try a date that's actually during the EDT part of the year.

regards, tom lane

On Thu, May 01, 2008 at 09:53:41AM -0400, Andrew Chernow wrote:

I am confused about the below results. The backend is in EDT but it is
converting timestamps into EST ... excluding NOW(). Regardless of the
timezone provided, the backend is dishing out EST.

First, this doesn't really belong on hackers, which is for hacking of
the back end. If you're going to follow up, please take it to
-general or something.

Your client thinks it's in America/Toronto or something similar. You
can set the time zone you're in by SET TIME ZONE (or timezone).
There's a lot more about this in the manual at
<http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html#DATATYPE-TIMEZONES&gt;

postgres=# select now();
now
-------------------------------------
Thu May 01 09:28:53.164084 2008 EDT

May is in Daylight time in (AFAIK all) Eastern zones.

postgres=# select '1997-01-29 12:31:42.92214 EDT'::timestamptz;
timestamptz
------------------------------------
Wed Jan 29 11:31:42.92214 1997 EST

postgres=# select '1997-01-29 12:31:42.92214 PST'::timestamptz;
timestamptz
------------------------------------
Wed Jan 29 15:31:42.92214 1997 EST

January is in Standard time in Eastern zones. Note that you asked for
1997-01-29 12:31:42.92214 EDT, and got back what time that would be
_for your actual timezone_. Same thing for the Pacific case.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

Tom Lane wrote:

Andrew Chernow <ac@esilo.com> writes:

I am confused about the below results. The backend is in EDT but it is
converting timestamps into EST ... excluding NOW(). Regardless of the
timezone provided, the backend is dishing out EST.

Try a date that's actually during the EDT part of the year.

regards, tom lane

Different systems do different things with Daylight time. For instance:
NTFS adjusts winter file times while in daylight savings (A file time of
Jan 20 6PM reads Jan 20 7PM while in Daylight Time). Whether that is
good or bad is a different story. I don't really have a problem with
either, just needed a little clarity.

Thanks,
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

Andrew Chernow wrote:

Tom Lane wrote:

Andrew Chernow <ac@esilo.com> writes:

I am confused about the below results. The backend is in EDT but it
is converting timestamps into EST ... excluding NOW(). Regardless
of the timezone provided, the backend is dishing out EST.

Try a date that's actually during the EDT part of the year.

regards, tom lane

Different systems do different things with Daylight time. For
instance: NTFS adjusts winter file times while in daylight savings (A
file time of Jan 20 6PM reads Jan 20 7PM while in Daylight Time).
Whether that is good or bad is a different story. I don't really have
a problem with either, just needed a little clarity.

The only file times we should ever be interested in are surely epoch
times, which should be unaffected by time zones.

cheers

andrew

NTFS adjusts winter file times while in daylight savings

The only file times we should ever be interested in are surely epoch
times, which should be unaffected by time zones.

cheers

andrew

epoch, or at least non-timezone adjusted times, is the way every modern
FS stores file times, no one said otherwise. There is a big difference
between time storage and display ... I was talking about display.
Postgres deals with both so at some point, a file time (or any time)
will get displayed. There are different ways of handling daylight
display, I have yet to find a technically correct way of doing it.

The more I think about it, I personally like the display behavior of
NTFS file times over something like EXT3. When I am in EDT, it is
useful to have all display times in that zone (regardless of whether
that time falls within winter or summer) ... just as they would if I
switched to PST.

--
andrew chernow
eSilo, LLC.
--every bit counts

On Thu, May 01, 2008 at 09:58:09PM -0400, Andrew Chernow wrote:

The more I think about it, I personally like the display behavior of NTFS
file times over something like EXT3. When I am in EDT, it is useful to
have all display times in that zone (regardless of whether that time falls
within winter or summer) ... just as they would if I switched to PST.

There's a difference between the two cases you're thinking of, though.

In the case of "you being in EDT", you didn't actually switch time
zone. In Eastern time zones, the offset is -05 from UTC for part of
the year, and -04 from UTC from part of the year. That's what it
_means_ to be in Eastern time: you didn't really switch time zone at
all. I'm in America/Toronto in January and in June. My display
should show the time correct to my time zone, not according to the
offset from UTC. If you really want that, change your time zone to be
an offset from UTC rather than a particular zone. (We used to have to
differentiate between EST and EDT during the summer months, because
Indiana didn't switch. As far as I know, there is no longer a single
jurisdiction where the summer time change doesn't happen in Eastern
time. If not for historical reasons, I'd argue the name should be
changed to Eastern time.)

If you switch to Pacific time (and why is it that people say "EDT" but
"PST"? I don't know of any Pacific time jurisdictions that don't
switch, either. Arizona doesn't switch mostly, but they're in
Mountain time), the same thing happens.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

Andrew Sullivan wrote:

On Thu, May 01, 2008 at 09:58:09PM -0400, Andrew Chernow wrote:

The more I think about it, I personally like the display behavior of NTFS
file times over something like EXT3. When I am in EDT, it is useful to
have all display times in that zone (regardless of whether that time falls
within winter or summer) ... just as they would if I switched to PST.

There's a difference between the two cases you're thinking of, though.

In the case of "you being in EDT", you didn't actually switch time
zone. In Eastern time zones, the offset is -05 from UTC for part of
the year, and -04 from UTC from part of the year. That's what it
_means_ to be in Eastern time: you didn't really switch time zone at
all. I'm in America/Toronto in January and in June. My display
should show the time correct to my time zone, not according to the
offset from UTC. If you really want that, change your time zone to be
an offset from UTC rather than a particular zone. (We used to have to
differentiate between EST and EDT during the summer months, because
Indiana didn't switch. As far as I know, there is no longer a single
jurisdiction where the summer time change doesn't happen in Eastern
time. If not for historical reasons, I'd argue the name should be
changed to Eastern time.)

If you switch to Pacific time (and why is it that people say "EDT" but
"PST"? I don't know of any Pacific time jurisdictions that don't
switch, either. Arizona doesn't switch mostly, but they're in
Mountain time), the same thing happens.

A

I prefer offset from UTC, the timezone abbrevs are ambiguos and
confusing. If I am in a timezone that is currently 4 hours behind UTC,
I would prefer all times to display adjusted by that offset. I
understand that technically its eastern time and EDT makes no sense in
Jan (NOTE: linux 'date' command rejects this ... should the backend?),
but its friendlier to adjust by a single offset (at least me thinks).
The individual's perception of time is -0400, thus displaying all times
adjusted by that seems logical. My whole preference here is in regards
to display times. I always try to see things from a non-technical
end-user's perspective.

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

Andrew Chernow wrote:

I prefer offset from UTC, the timezone abbrevs are ambiguos and
confusing. If I am in a timezone that is currently 4 hours behind
UTC, I would prefer all times to display adjusted by that offset. I
understand that technically its eastern time and EDT makes no sense in
Jan (NOTE: linux 'date' command rejects this ... should the backend?),
but its friendlier to adjust by a single offset (at least me thinks).
The individual's perception of time is -0400, thus displaying all
times adjusted by that seems logical. My whole preference here is in
regards to display times. I always try to see things from a
non-technical end-user's perspective.

Well, I think you're failing in this instance :-). If I see a time that
says 4.00 am June 27th tzname I understand it to be with the offset that
was current in that time zone *at that time*. And I certainly don't want
it to change depending on what the *current* offset is in that time zone
- I want it always to display the same. I bet if you take a random
sample of non-technical end-users you'll find that's exactly what the
vast majority want.

cheers

andrew

On Fri, May 02, 2008 at 09:54:20AM -0400, Andrew Chernow wrote:

I prefer offset from UTC, the timezone abbrevs are ambiguos and confusing.

So did you read the manual on SET TIME ZONE?

TIME ZONE

SET TIME ZONE value is an alias for SET timezone TO value. The
syntax SET TIME ZONE allows special syntax for the time zone
specification. Here are examples of valid values:

'PST8PDT'

The time zone for Berkeley, California.
'Europe/Rome'

The time zone for Italy.
-7

The time zone 7 hours west from UTC (equivalent to
PDT). Positive values are east from UTC. INTERVAL '-08:00' HOUR
TO MINUTE

The time zone 8 hours west from UTC (equivalent to PST).

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/