Re: BUG #6001: date_trunc is not timezone aware

On Sun, May 01, 2011 at 06:48:57PM +0000, Marek Nos wrote:
>
> The following bug has been logged online:
>
> Bug reference: 6001
> Logged by: Marek Nos
> Email address: marek(dot)nos(at)centrum(dot)cz
> PostgreSQL version: 9.0
> Operating system: Windows 7
> Description: date_trunc is not timezone aware
> Details:
>
> and other like extract() as well
>
> SELECT date_trunc('day', '2011-05-02 02:00:00+05'::timestamptz)
>
> I would expect result as
> '2011-05-02 00:00:00+05'
> but
> '2011-05-01 00:00:00+02' is given (pre conversion to server timezone is done
> first)

because you didn't specify which timezone you *want*.

when you're connecting to server, and not providing your own time zone,
it is assumed that you're using the same time zone as server.

and all timestamptz, in all time zones, are converted to your time zone:

$ show timezone;
TimeZone
----------
Poland
(1 row)

$ select now();
now
------------------------------
2011-05-02 12:43:56.56492+02
(1 row)

$ SELECT '2011-05-02 02:00:00+05'::timestamptz;
timestamptz
------------------------
2011-05-01 23:00:00+02
(1 row)

To make the calculations work in different time zone, simply set it:

$ set timezone = '+05';
SET

$ select now();
now
-------------------------------
2011-05-02 15:44:38.044978+05
(1 row)

$ SELECT '2011-05-02 02:00:00+05'::timestamptz;
timestamptz
------------------------
2011-05-02 02:00:00+05
(1 row)

$ SELECT date_trunc('day', '2011-05-02 02:00:00+05'::timestamptz);
date_trunc
------------------------
2011-05-02 00:00:00+05
(1 row)

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/