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.
