Postgres Pro
Downloads
Home   >   mailing lists

Thread: A simpler time zone question

A simpler time zone question

From
"Rob Richardson"
Date:

I see that the query “select '2011-11-6 00:59'::timestamptz’” returns a timestamptz with a time zone of -4, which is correct, since I’m in the Eastern time zone and the change from EDT to EST will happen at 2011-11-6 02:00.  The query “select '2011-11-6 01:01'::timestamptz” gives me a time zone offset of -5, which tells me that PostgreSQL assumes that the time change has already happened.  Can I count on that behavior?  Will PostgreSQL always assume that an ambiguous time is in standard instead of daylight time?

 

Thanks again!

 

RobR

Re: A simpler time zone question

From
Tom Lane
Date:
"Rob Richardson" <Rob.Richardson@rad-con.com> writes:
> Will PostgreSQL always assume that an ambiguous time is in
> standard instead of daylight time?

Yes, I believe that's even documented somewhere.  I think it will also
do that if the time is impossible (eg, 02:30 during a forward DST jump)

            regards, tom lane

Re: A simpler time zone question

From
Steve Crawford
Date:
On 03/28/2011 08:57 AM, Tom Lane wrote:
> "Rob Richardson"<Rob.Richardson@rad-con.com>  writes:
>> Will PostgreSQL always assume that an ambiguous time is in
>> standard instead of daylight time?
> Yes, I believe that's even documented somewhere.  I think it will also
> do that if the time is impossible (eg, 02:30 during a forward DST jump)
>
>             regards, tom lane
>
I'd love a link to the documentation specifying that behavior. I've
spent a while searching and have thus-far failed to locate it.

If missing, it should be added.

Cheers,
Steve

Re: A simpler time zone question

From
Tom Lane
Date:
Steve Crawford <scrawford@pinpointresearch.com> writes:
> On 03/28/2011 08:57 AM, Tom Lane wrote:
>> Yes, I believe that's even documented somewhere.  I think it will also
>> do that if the time is impossible (eg, 02:30 during a forward DST jump)

> I'd love a link to the documentation specifying that behavior. I've
> spent a while searching and have thus-far failed to locate it.

Hmm.  The code is perfectly clear about it, see DetermineTimeZoneOffset:

    /*
     * It's an invalid or ambiguous time due to timezone transition. Prefer
     * the standard-time interpretation.
     */

but right offhand I don't see any mention of ambiguous times in the
likely parts of the documentation.

            regards, tom lane