Re: Timezone information - Mailing list pgsql-general

On 02/19/2014 01:52 PM, Dev Kumkar wrote:
> On Thu, Feb 20, 2014 at 2:49 AM, Andrew Sullivan <ajs@crankycanuck.ca
> <mailto:ajs@crankycanuck.ca>> wrote:
>
>     No, select now() would return the time in whatever timezone is set, or
>     the timezone that the server defaulted to if there's nothing set by
>     the client.  So in your installation, set up the server to use UTC by
>     default and, if you like, set the client's time zone according to
>     locale or whatever when the client connects.
>
>
> In my case the timezone parameter gets set. If I comment/remove it then
> UTC is returned by default. So server here defaulted to UTC.
> Executed Select now() from pgAdmin and psql, time gets returned in UTC.
>
>     The timestamps in the server are not actually "in" a time zone.
>     They're all stored as UTC, and the display is altered according to
>     what your time zone settings are at the time of query.
>
>
> Hmm. Missed one observation here, created a test table with timestamp
> column of type 'default current_timestamp'.
> When the query is executed from JDBC then it stores OS specific local
> time into this column.

It depends on how you are declaring the timestamp field. If you do not
use with time zone then the input value is open to interpretation and is
not  'anchored' to a point in time.

Example

My time zone is currently PST.

test=> create table timestamp_test(id int, ts timestamp, ts_z timestamp
with time zone);
CREATE TABLE

test=> insert into timestamp_test values (1, now(), now());
INSERT 0 1

test=> select * from timestamp_test ;
  id |            ts             |             ts_z
----+---------------------------+------------------------------
   1 | 2014-02-19 14:37:17.53107 | 2014-02-19 14:37:17.53107-08
(1 row)

test=> select ts at time zone 'UTC', ts_z at time zone 'UTC' from
timestamp_test;

            timezone           |         timezone


------------------------------+---------------------------


  2014-02-19 06:37:17.53107-08 | 2014-02-19 22:37:17.53107


(1 row)




If you know what time zone the value was inserted under you can get it back.

test=> insert into timestamp_test values (1, now(), now());
INSERT 0 1
test=> select * from timestamp_test ;
  id |             ts             |             ts_z
----+----------------------------+-------------------------------
   1 | 2014-02-19 14:57:35.418363 | 2014-02-19 14:57:35.418363-08
(1 row)

test=> select ts at time zone 'PST', ts_z at time zone 'PST' from
timestamp_test;
            timezone            |          timezone
-------------------------------+----------------------------
  2014-02-19 14:57:35.418363-08 | 2014-02-19 14:57:35.418363
(1 row)


That assumes a lot, so the best thing is to use timestamp with time zone.

>
> However when the same query is executed from ODBC then it behaves either
> as per the timezone set in postgreSQL.conf or when not set then UTC. So
> looks like am missing some setting while executing query from ODBC. Btw
> also the pgAdmin and psql behave same as ODBC case. What am missing here
> which JDBC is doing correctly.
>
> Regards...


--
Adrian Klaver
adrian.klaver@aklaver.com