SEO

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.

[root@dev43 ~]# uname -a
2.6.9-67.0.4.EL #1 Sun Feb 3 06:53:29 EST 2008 i686 athlon i386 GNU/Linux

[root@dev43 ~]# date
Thu May 1 09:54:17 EDT 2008

postgres=# select version();
PostgreSQL 8.3devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.4.6 20060404 (Red Hat 3.4.6-8)

postgres=# set datestyle='Postgres, MDY';
postgres=# select now();
now
-------------------------------------
Thu May 01 09:28:53.164084 2008 EDT

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

Is this expected behavior? I am not sure if I am missing something or
my results are wrong. Is there a setting that needs tweaking?

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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>

> 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/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

>>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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers