Re: Timezone difference between Oracle SYSDATE and PostgreSQL timestamp functions

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