current_timestamp is incorrect?

Answered

I have a very worrisome issue that I cannot find any information about.  This is for Postgres databases.  We have servers on UK time and when I run SELECT current_timestamp, it gives me local time instead of UK time.  When I run the same statement using command line or pgAdmin with the same connection to the same server, I get UK time.  Please any information?  I thought that this query runs on the server.  This is obviously a huge issue - any insight is much appreciated!

9 comments
Comment actions Permalink

On client side you need to set timezone, since jdbc driver doesn't know anything about server's time zone

1
Comment actions Permalink

Hello Jeremy,

What JDBC driver version fo you use (you can see it by pressing Test Connection in Properties for created Data Source. Users report that there could be changes in this regard between 9.0 and 9.2 versions. Please check this thread for more information http://stackoverflow.com/q/18447995/2000323 .

0
Comment actions Permalink

jdbc_version.jpg
Is there no way to change this setting?  I love 0xDBE but this is almost a deal breaker.  I can't having my scripts run differently on command line and through JDBC.

0
Comment actions Permalink

You can change driver for Postgres Data Source here:
2015-04-22_18-56-14.png

0
Comment actions Permalink

I don't know that I want to have an older driver version with a newer postgres version.  This seems to be a Java problem.  There should be a setting for local vs. server time.

0
Comment actions Permalink

All options supported by driver (or your custom option) are listed and can be set in Advanced tab of Data Source settings dialog. Does it make a difference if set compatibiilty to 9.0 version?
Data_Sources_and_Drivers_2015-04-23_14-28-13.png

You can also specify timezone for Java process in VM options field at the bottom at the same tab, for example  "-Duser.timezone=GMT+2"

0
Comment actions Permalink

I am running into the same issue. Works fine in pgadmin, but data grip is returning a time 5 hours in the future reletative to where I am. to use the vm options, would I just paste the plain text? Also, how would I compensate for day light savings time. I would need it to switch automatically between EST and EDT. 

0
Comment actions Permalink

Are you going to fix it?

0
Comment actions Permalink

This is troublesome. Using psql on server command line, where server is MDT:

SELECT TIMESTAMP '2016-09-01 10:12:15' AT TIME ZONE 'America/Denver';
yields
timezone
------------------------
2016-09-01 10:12:15-06

which per the postgres docs is correct. It converts the time to Denver time zone and then displays in the local time zone (which is also Denver) with the zone indicator '-06'

But running the same query in DataGrip against the same server, yields

timezone
------------------------
2016-09-01 16:12:15

which seems to imply there is a conversion by the jdbc driver to UTC, meaning it is altering the data as returned by the server.

However, following the advice above, adding my timezone to the VM Options for the PostgreSql Driver fixed the issue. (Adding it to datagrip64.exe.vmoptions did NOT fix it)

 

0

Please sign in to leave a comment.