Postgres: Showing Timestamp With Timezone in UTC

Hi!

After you edit vm options you need to reopen table editor/console. Does it helps?

I am having similar issues.  

Firstly, to clarify you want to add -Duser.timezone=UTC (NO QUOTES) to the vm options it seems.  Yes, this also works after closing the Console window and refreshing the connection.

There still seems to be a serious bug here. I'm in PDT Time, and we have some specific needs for this to work, otherwise we can't roll this out to the organization.

Once the configuration is changed, this syntax should work, but the Critical one is incorrect.

SELECT
  NOW() AS "Now - UTC",
  NOW()::TIMESTAMPTZ AS "Now - TIMESTAMPTZ - UTC",
  NOW()::TIMESTAMP AS "Now - Timestamp - Local",
  (NOW()::TIMESTAMPTZ AT TIME ZONE 'PST8PDT') AS "Now - PDT"

Thank you for mentioning the vm option!

As I was writing migrations for my application in the SQL console it turned out that this setting at least moves timestamps (without timezone) without tampering with the time in the process. I verified by outputting date and time in the application itself and crosschecking in pgAdmin3.

Results in PHPStorm are still shown in the wrong (vm client) timezone when browsing.

This is a deal breaker for me. Leaving DataGrip until this is fixed.

There are inconsistencies between how date data is listed in the data view tables, and query results.

Adding -Duser.timezone=UTC to VM options "fixes" the table views, but not query results.

Other solutions, like Navicat, lists the expected values out of the box, both in the table list views and the query results.

I just burned 2 hours before I figured out that the the database tools in PyCharm are showing the wrong time.  When I run the query in PG Admin I properly get 2017-06-28 00:55:54 but DataGrip/PyCharm Database Tools is showing 2017-06-27 23:55:54.000000.

If this is a bug then it needs to be fixed right away as it makes the tool worthless for anything with a datetime.

Seconded! This is really ridiculous as a default set of parameters.  We just wasted hours trying to trace down inconsistencies.

Thirded. Completed my evaluation and my lone decision not to purchase was because of the inconsistencies. After 10 hours wasted, I found this thread and fixed SOME of the problems by using the VM parameters, but not all. 

+1 to that, i do not know why force the format of the dates, this problem, plus the fact that if one db is offline i can not synchronize the other databases because the process fails it is a really big let down to use the tool

Hello, all,

We have plans to implement more comfortable way to work with time zone. Please vote and follow these requests in our tracker: https://youtrack.jetbrains.com/issue/DBE-2996 / https://youtrack.jetbrains.com/issue/DBE-3676

@Larmac-8 I'm running version 2017.2.2 and that VM options fix does apply to queries too.

The vm option -Duser.timezone=UTC does not work anymore for me (PHPStorm 2017.3).

Postgres' timestamp(0) with time zone columns went back to not showing the timezone modifier at the end.

All of this makes programming with dates even more frustrating than it already is.

Datagrip - Did the same thing just spent hours tracking down an issue that only appeared to be a datagrip date issue.  Why does every other product use the local computer offset except this one?  Frustrating... please fix.

it's now been almost 2 years, I'm at the latest version (2018.1.5), and still no fix for this issue. DbVisualizer (and many other tools) don't have this issue.

Currently all timestamps are showing in UTC by default. If you don't see offset in timestamp field it also means that it is in UTC.

Still broke!

Same problem, albeit for MariaDB.  I have a DATE column that contains values like "2018-09-30".  Per MariaDB/MYSQL documentation, DATE columns are not supposed to involve time zone adjustments in any event.  Values go in and come out as pure dates, without adjustment.

When I view the table phpMyAdmin shows dates as, for instance, "2018-09-30".  

But using:

PhpStorm 2018.2.5
Build #PS-182.4892.16, built on October 11, 2018
JRE: 1.8.0_152-release-1248-b8 amd64
JVM: OpenJDK 64-Bit Server VM by JetBrains s.r.o
Linux 4.15.0-38-generic

I see "2018-09-29" in the same column, whether I browse or query.  I am in UTC -5:00, so presumably PHPStorm is showing me the recorded date as if it were GMT, and converting to my local time.  Adding -Duser.timezone=UTC to the VM options of the connection has no effect, even after re-starting PHPStorm.  

UPDATE:  I went to Help/Update Custom VM Options, created a custom set of vm options (which I did not have before), added -Duser.timezone=UTC to the custom VM options, and it solved the problem.  I don't know what else will be messed up now, but hey.  It is still pretty annoying that the DataGrip component is adding a timezone adjustment to a type of data that is not supposed to incorporate time zone adjustments to begin with.

I'm confused on why I would want any IDE to convert dates out of sql into my local time for display.  If i'm doing a query to pull rows back and looking at the data, I don't want any translation or changes to be done on what is shown in the results, I want to see what is in the database.  This was a maddening journey trying to figure out what was wrong before I got to it being a datagrip problem.  Setting the vm options as suggested does nothing to fix what I am experiencing.

Running this in snowflake:

SELECT '2018-01-14' :: DATE AS d
FROM table(generator(rowcount => 1)) a;

Yields: 2018-01-13

(╯°□°）╯︵ ┻━┻

Hey Scott Vickers:  In case it helps, you should note that you can change the VM options 1) in the properties of the database connection, and 2) for the IDE as a whole (Help/Edit Custom VM Options).  #1 did nothing for me either.  But #2 did work. All that being said, this is still a messed up result.

@tech-tamer That works! Thanks for the heads up, had missed that in your other comment.

The huge reason for running queries at a specific is had to do with correct grouping on time functions.  In our case, we need group based on local time of when the data was collected.  Running queries that group at GMT or any other timezone provides the wrong results.  I still find it troublesome and difficult the way datagrip has decided to implement this.  I love data-grip in many but we don't have have these tz issues with timestamps with other any other sql tools.

The more appropriate way to probably run this is reflect this time to the desired tz prior to aggregation in the query but this sometimes not intunitve.   So if I had a request for data grip, it would be for a sql-execution-time option to assign the desired tz the query should use.

I suggest changing the title of this issue.  It is not just a Postgres issue, but also affects, at least, MYSQL and MariaDB.

I had the same issue as Tech-tamer as of 2019/04/29. 

I was using the MARIADB connector 2.4.1, I had both a DATE and a TIME column which should be timezone-naive, however when displaying the results, phpStorm was facing an interpretation disagreement between DATE and TIME: 

• When not using -Duser.timezone, the TIME showed correctly but the DATE was wrongly offsetted.
• After setting -Duser.timezone, the TIME was now offsetted and the DATE was good. 

My solution was to use the MySQL connector on my mariadb table, and it all worked seamlessly without touching any of the VM params. That is as of 4/29/2019.

I was hoping the MariaDB connector was OK to use, but not yet. Will try to switch to it again next year. 

Hope it saves someone some time. 

I am having the same issue over here. Tried the VM proposed fix both for the database and for the general configuration but they did not work for me.

I will try to re-install the software and re-connect as a last resource. If it doesn't work, then I will uninstall it for good as such odd behaviour is generating a good number of issues.

Edit on software version:

DataGrip 2021.1
Build #DB-211.6693.68, built on March 31, 2021
Licensed to Juan Martin Sanguinetti
Subscription is active until March 15, 2022.
For educational use only.
Runtime version: 11.0.10+9-b1341.35 x86_64
VM: Dynamic Code Evolution 64-Bit Server VM by JetBrains s.r.o.
macOS 11.4
GC: ParNew, ConcurrentMarkSweep
Memory: 1979M
Cores: 16
Non-Bundled Plugins: bundled-datagrip-help (211.6693.68)

Juanmartin Sanguinetti  what is your database?For MySQL and PG and certain others  if you open up datasource properties and go to Options tab you'll see 'Timezone' field, where you can specify desired timezone.

@... Its Snowflake. In the options tab, there is no timezone field. Might be the case that this bug was solved for other databases and this one is relatively new?

for Snowflake the only way to specify timezone is to use command like this

• alter session set timezone = 'Australia/Hobart';

or as far as I know timezone can be assigned to login in Snowflake control panel.

@... Yuriy, thank you for your reply. I tried running that line in the console and despite being executed it didn't correct the issue. The situation is like this, out of 5 accessing the same Snowflake database, 3 of us were using Datagrip and only 2 of us were having this issue (1 did not, even using Datagrip). The others are using different software and have no issues whatsoever.

It is difficult for me to understand why the software would be transforming any value I am trying to read from a database. Makes no sense to me what would be the use case for something like this. When I configured it, at no point I was prompted with any timezone details so took me a while to identify that there was an issue going on of this date off set. Just by coincidence checked a calendar a noticed that the date did not match the day of the week.

it looks like known issue - please check. If this is your case - fix will be available in next update, or now you can install our EAP version.

@... thank you! It seems to be the same issue. Im glad that has been identified and looking forward to the next update then. Thanks.