Postgres: Showing Timestamp With Timezone in UTC

When I select from a table with a TIMEZONE WITH TIMESTAMP column, all the dates in that column are displayed in my local timezone.

pgAdmin shows the times in utc.

I have tried going to the connection properties of my Postgres conneciton, then advanced, and entering "-Duser.timezone=UTC" for the VM options, but it doens't seem to make a difference in my queries.

22 comments
Comment actions Permalink

Hi!

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

0
Comment actions Permalink

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"





2
Comment actions Permalink

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.

0
Comment actions Permalink

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.

1
Comment actions Permalink

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.

4
Comment actions Permalink

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

3
Comment actions Permalink

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. 

0
Comment actions Permalink

+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

0
Comment actions Permalink

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-2996https://youtrack.jetbrains.com/issue/DBE-3676

0
Comment actions Permalink

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

1
Comment actions Permalink

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.

0
Comment actions Permalink

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.

0
Comment actions Permalink

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.

0
Comment actions Permalink

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.

1
Comment actions Permalink

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.

1
Comment actions Permalink

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

(╯°□°)╯︵ ┻━┻

3
Comment actions Permalink

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.

1
Comment actions Permalink

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

0
Comment actions Permalink

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.

0
Comment actions Permalink

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

0
Comment actions Permalink

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. 

0

Please sign in to leave a comment.