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!
Please sign in to leave a comment.
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 .
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.
You can change driver for Postgres Data Source here:
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.
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?
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"
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.
Are you going to fix it?
This is troublesome. Using psql on server command line, where server is MDT:
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
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)
On client side you need to set timezone, since jdbc driver doesn't know anything about server's time zone
