Thread: Timezone difference between Oracle SYSDATE and PostgreSQL timestamp functions

Hello,
We have converted Oracle SYSDATE to PostgreSQL statement_timestamp() but
there is a difference in timezone.
SYSDATE returns the time on the server where the database instance is
running(returns operating system time) so the time depends on the OS
timezone setting.
while the timezone of postgreSQL
statement_timestamp()/now()/clock_timestamp() depends on the DBMS setting.
so I think timezone settings are different between DBMS and OS.
Consider the following example
SYSDATE vs CURRENT_DATE:
SYSDATE returns the time on the server where the database instance is
running(returns operating system time) CURRENT_DATE returns the time where
the session is running In the below example sysdate and current_date return
the same time but if we set the new time zone then it shows the difference
in time.

Example:
Oracle:
SQL> select SYSDATE,CURRENT_DATE from dual;

SYSDATE             CURRENT_DATE
--------------------------- ---------------------------
28-AUG-14 14:08:58        28-AUG-14 14:08:58

SQL> ALTER SESSION SET TIME_ZONE = '-5:0';
SQL>select SYSDATE,CURRENT_DATE from dual;

SYSDATE             CURRENT_DATE
--------------------------- ---------------------------
28-AUG-14 14:10:23        28-AUG-14 03:40:23

PostgreSQL:
postgres=# show time zone;
   TimeZone
--------------
 Asia/Kolkata
(1 row)

postgres=# select now();
               now
----------------------------------
 2014-08-28 14:19:51.740664+05:30
(1 row)

postgres=# set time zone 'Europe/Rome';
SET
postgres=#
postgres=# select now();
              now
-------------------------------
 2014-08-28 10:51:03.941594+02
(1 row)

Any idea how can we set OS timezone on PostgreSQL?



-----
Thanks and Regards,
Vinayak Pokale,
NTT DATA OSS Center Pune, India
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Timezone-difference-between-Oracle-SYSDATE-and-PostgreSQL-timestamp-functions-tp5816851.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 8/28/2014 10:06 PM, Vinayak wrote:
> Any idea how can we set OS timezone on PostgreSQL?

timestamp with time zone values are stored as the equivalent of UTC and
converted to the client TIME_ZONE setting for display, unless you
specify some other timezone via AT TIME ZONE

I'm not sure why any other behavior would be rational.


--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Vinayak wrote:
> We have converted Oracle SYSDATE to PostgreSQL statement_timestamp() but
> there is a difference in timezone.
> SYSDATE returns the time on the server where the database instance is
> running(returns operating system time) so the time depends on the OS
> timezone setting.
> while the timezone of postgreSQL
> statement_timestamp()/now()/clock_timestamp() depends on the DBMS setting.
> so I think timezone settings are different between DBMS and OS.

[...]

> Any idea how can we set OS timezone on PostgreSQL?

If you mean the *server's* OS timezone, I guess you'd have to write
a C function that does something similar to identify_system_timezone()
in bin/initdb/findtimezone.c.

Yours,
Laurenz Albe

On 08/28/2014 10:06 PM, Vinayak wrote:
> Hello,
> We have converted Oracle SYSDATE to PostgreSQL statement_timestamp() but
> there is a difference in timezone.
> SYSDATE returns the time on the server where the database instance is
> running(returns operating system time) so the time depends on the OS
> timezone setting.
> while the timezone of postgreSQL
> statement_timestamp()/now()/clock_timestamp() depends on the DBMS setting.
> so I think timezone settings are different between DBMS and OS.
>
> Any idea how can we set OS timezone on PostgreSQL?
>
If you mean setting the default time zone for interpreting non-qualified
input and displaying output, start with the 'timezone' setting in
postgresql.conf. Most installs have that default to 'localtime' which
means to use the servers local timezone but you can set it to whatever
timezone you prefer. Absent an override by the client, this will be the
default.

Next, the PGTZ environment variable can set a local default for clients
reading that variable. Finally, that can then be overridden within a
connection through the 'set time zone...' statement.

Cheers,
Steve

Thank you for reply.

The time return by SYSDATE depends on the OS timezone setting while in
PostgreSQL we can set the timezone using 'set time zone..' statement so here
timezone setting depends on DBMS but I think there are not so many systems
that use different timezone settings between DBMS and OS.





-----
Regards,
Vinayak,

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Timezone-difference-between-Oracle-SYSDATE-and-PostgreSQL-timestamp-functions-tp5816851p5817107.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 08/31/2014 09:22 PM, Vinayak wrote:
> Thank you for reply.
>
> The time return by SYSDATE depends on the OS timezone setting while in
> PostgreSQL we can set the timezone using 'set time zone..' statement so here
> timezone setting depends on DBMS but I think there are not so many systems
> that use different timezone settings between DBMS and OS.

I would say that needs clarification. If you have a database running on
a server and getting its timezone from the server OS and multiple
clients running across many timezones each with a different OS timezone,
then the above is not strictly true. So, exactly which machine OS are
you talking about?

>
>
>
>
>
> -----
> Regards,
> Vinayak,
>
> --



--
Adrian Klaver
adrian.klaver@aklaver.com