Re: Strange behavior in generate_series(date, date, interval) with DST

From: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Strange behavior in generate_series(date, date, interval) with DST
Date: 2014-12-08 01:05:15
Message-ID: 20141208010513.GA21788@crankycanuck.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Sun, Dec 07, 2014 at 08:25:48PM -0200, Sérgio Saquetim wrote:
>
> I wasn't paying attention to the fact that generate_series really expects
> for timezone inputs. So when I was passing the upper bound
> as '2014-10-20'::DATE, the value was being cast to 2014-10-20 00:00:00-02.
>
> postgres=# SELECT '2014-10-20'::TIMESTAMPTZ;
> timestamptz
> ------------------------
> 2014-10-20 00:00:00-02
> (1 row)

[…]

> Using a larger upper bound solved my problem.

As a more general lesson, I'd suggest that when you're working with
dates your best bet is to do it with your time zone as UTC. If you
then want to format the output in the local time zone, you can do that
in the outer SELECT with AT TIME ZONE. This isn't because Postgres is
going to get this wrong, but because it's far too easy to confuse
yourself with those time changes. It makes debugging easier,
particularly because the time change only happens twice a year so
nobody _ever_ thinks of it when troubleshooting.

Best regards,

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca

In response to

Browse pgsql-general by date

  From Date Subject
Next Message David G Johnston 2014-12-08 02:18:11 Re: FW: SQL rolling window without aggregation
Previous Message AJ Welch 2014-12-08 00:42:08 Re: FW: SQL rolling window without aggregation