Re: Odd timezone backend output

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(at)commandprompt(dot)com
+1 503 667 4564 x104
http://www.commandprompt.com/