Change Timezone from local to UTC

[leo...@perxhealth.com]

My db is working with UTC/GMT timezone but Workbench is converting datestamps to my local time, GMT+10. I'd like to get the results in UTC/GMT.

I've tried adding an extended property when launching the application with: -Duser.timezone=GMT but it doesn't seem to be working, as the function 

SELECT now() is still bringing me local time (GMT+10). Please see screenshot attached.

Any idea why this is working? How can I make sure the default timezone on Workbench is UTC/GMT?

Thanks!

[Thomas Kellerer]

Which DBMS are you using?
Which JDBC driver and version are you using?
Which Workbench build are you using?

Thomas

[leo...@perxhealth.com]

Thanks for the reply Thomas. Here is the information:

- We're using PostgreSQL

- JDBC driver is postgresql-42.2.2

- Workbench build 123

Thanks!

[Thomas Kellerer]

Postgres adjusts the values for a "timestamp with time zone" (which is what now() returns) to the session's time zone.
This happens on the server (at least by default) and thus SQL Workbench only sees the adjusted value.

I think the JDBC driver honers Java's "user.timezone" and adjusts the session time zone accordingly. But the way you specified that, does not change the "user.timezone" property. It creates a property with the name "time zone".

The syntax "-Duser.timezone=GMT" is intended to be passed on the command line when starting a Java VM.
So you can add -Duser.timezone=GMT to the commandline when you start SQL Workbench (e.g. passing it to the exe)

I think defining that through the extended properties should work, but the property name (left column) has to be "user.timezone" and the value should be "GMT" (without the quotes obviously).

You can also try changing the session time zone directly using,

set timezone='GMT';

If you are dealing with time zones you should add a placeholder for the time zone to the formatting options, e.g. "yyyy-MM-dd HH:mm:ss z",
then the value would be displayed as: e.g. "2018-07-12 12:36:55 GM"

What exactly are you seeing when you run "select now()" in psql?
That should be the same display as in SQL Workbench (at least I tried quite hard to achieve that).

Regards
Thomas

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Guy Rouillier]

You can also apply this setting globally by creating an environment
variable JAVA_TOOL_OPTIONS set to "-Duser.timezone=GMT".

--
Guy Rouillier

>an email to sql-workbenc...@googlegroups.com.

[leo...@perxhealth.com]

Thanks Thomas. I couldn't pass it as an extended property, even with the syntax you said, but I could do it by passing the timezone on the command line. But my concern is that I'd have to do this every time I open Workbench which is a bit painful. Is there a way to set this as an enduring preference?

> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbench+unsub...@googlegroups.com>.

[leo...@perxhealth.com]

Hi Guy, can you give me a bit more detail on how to do what you say? I'm a complete beginner, so appreciate any help you can give.

Thanks!

>><mailto:sql-workbench+unsub...@googlegroups.com>.

[Guy Rouillier]

I just reread your original message, and see you don't identify what operating system you are using.  I assumed you are using some variety of Windows.  If so, here is how to set JAVA_TOOL_OPTIONS globally.  I use Windows 7, but Windows 10 is similar:

Control Panel → System and Security → System → Advanced system settings → Environment variables

1. So, in the Start menu, click Control Panel, then System.
2. In the resulting dialog, click Advanced system settings on the left side.
3. In the resulting dialog, click the Environment Variables button
4. In the resulting dialog, scroll through the System variables on the bottom, looking for one called JAVA_TOOL_OPTIONS.  You probably don't have one; in that case, just click the New... button.  Otherwise, click the Edit... button.
5. In the resulting dialog, set the variable name to JAVA_TOOL_OPTIONS, and the variable value to "-Duser.timezone=GMT", without the double quotes.  If this variable already existed and you are editing, then add a space to the existing value, and then add "-Duser.timezone=GMT" on the end.
6. Click OK all the wait out of all the dialogs.

That's it.  You'll need to restart the app for it to pick up this new system variable.  If you are using Mac or Linux, say so and I or someone will provide the appropriate directions.