cancel
Showing results for 
Search instead for 
Did you mean: 

Return local time when calling sysdate()

SOLVED
Level 3 Adventurer

Return local time when calling sysdate()

We are in the process of converting from Oracle to EPAS.  We are running into a compatibility issues when calling sysdate().  Unfortunately, our application assumes that sysdate() returns the localtime of the server whereas postgres is returning UTC.  How can we config EPAS to return the local server time instead of UTC when calling sysdate() or other date functions by default.

 

If there is no way we will need to modify all our SQL to adjust.  if that is the case, what would be the simplest way to convert?

 

Thanks

Dave

1 ACCEPTED SOLUTION

Accepted Solutions
Level 3 Adventurer

Re: Return local time when calling sysdate()

We determined this to be an issue with the client that we are using.

 

I have found that the timezone setting in postgres behaves very strangely to a point that is very confusing.  While timezone can be set at the server it only changes the default for the connecting clients if they do not specify a zone.  However, this setting changes the behavior of datetime functions on the server.

 

That said using pSQL on the server returns the proper time in the current time zone but using datagrip (our client) returns time in UTC.  Even stranger is that when doing comments like 'select * from pg_settings where name like 'Time%' returns UTC in the settings field whereas it was suppose to return the setting in the server config file - wrong.

6 REPLIES
EDB Team Member

Re: Return local time when calling sysdate()

Hi davejung,

 

You will need to set the date and timezone parameters in the postgresql.conf according to the timezone you require. Setting these parameters will set the values at database level.

 

Refer the EPAS documentation present on the EDB website for more details.

https://www.enterprisedb.com/resources/product-documentation

 

 

Level 3 Adventurer

Re: Return local time when calling sysdate()

Yes, we already checked the configuration files.  And they are listed as US/Eastern

EDB Team Member

Re: Return local time when calling sysdate()

Hi davejung,

 

Please verify whether the parameter settings are set on the database, as it requires database service reload.

 

It would be helpful if you verify following test case :

 

edb=# select version();

                                                   version                                                    

--------------------------------------------------------------------------------------------------------------

EnterpriseDB 10.5.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit

(1 row)

 

Timezone setting for database :

edb=# show timezone;

  TimeZone 

------------

US/Eastern

(1 row)

 

Timezone set on operating system :

edb=# \! date

Sat Nov 17 17:19:36 UTC 2018

 

Database timezone outputs :

edb=# select now();

               now                

----------------------------------

17-NOV-18 12:19:49.065742 -05:00

(1 row)

 

edb=# select sysdate();

      sysdate       

--------------------

17-NOV-18 12:19:56

(1 row)

 

Correct us if you feel we are failing to understand your problem statement.

Level 3 Adventurer

Re: Return local time when calling sysdate()

So when I do a show timezone it returns UTC.

 

but when I do a 
grep --include=*.conf -rw '/var/lib/edb/as10/data' -e 'timezone'

it returns
/var/lib/edb/as10/data/postgresql.conf:timezone = 'America/New_York'

 

Also when I do a select setting from pg_settings where name like 'T%';

returns

UTC

 

Dave

Level 3 Adventurer

Re: Return local time when calling sysdate()

We determined this to be an issue with the client that we are using.

 

I have found that the timezone setting in postgres behaves very strangely to a point that is very confusing.  While timezone can be set at the server it only changes the default for the connecting clients if they do not specify a zone.  However, this setting changes the behavior of datetime functions on the server.

 

That said using pSQL on the server returns the proper time in the current time zone but using datagrip (our client) returns time in UTC.  Even stranger is that when doing comments like 'select * from pg_settings where name like 'Time%' returns UTC in the settings field whereas it was suppose to return the setting in the server config file - wrong.

Highlighted
EDB Team Member

Re: Return local time when calling sysdate()

Hi  davejung,

 

Glad that your issue is resolved. Thank you for explaining the client issue in detail.