Skip site navigation (1) Skip section navigation (2)

Re: BUG #6001: date_trunc is not timezone aware

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: Marek Nos <marek(dot)nos(at)centrum(dot)cz>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6001: date_trunc is not timezone aware
Date: 2011-05-02 10:45:14
Message-ID: 20110502104514.GA2709@depesz.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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/

In response to

pgsql-bugs by date

Next:From: Pavel StehuleDate: 2011-05-02 16:00:48
Subject: documentation bug - behave of NEW a OLD in plpgsql's triggers
Previous:From: Marek NosDate: 2011-05-01 18:48:57
Subject: BUG #6001: date_trunc is not timezone aware

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group