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

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