I have a column in the database (PostgreSQL). I want to insert the current time in GMT in this column. When getting the current time and inserting it into the DB it's inserted in the server timezone GMT-5 although that time was in GMT+0. Any ideas how to in开发者_开发问答sert this time in the database in GMT timezone?

Always UTC in Postgres

Understand that Postgres always stores values in a column of type TIMESTAMP WITH TIME ZONE in UTC, that is, with an offset from UTC of zero hours-minutes-seconds.

So the name is bit of a misnomer, in that no time zone is actually being stored. Instead, any time zone or offset indicator arriving with an input is used by Postgres to adjust to UTC. After adjustment, the UTC value is stored to the table while the zone/offset indicator is discarded. If you care about the original time zone, you must write that yourself to a second column.

What makes things confusing is tooling and middleware such as the psql console client app. These often are opinionated, choosing to inject some default time zone adjustment onto retrieved values. So while the Postgres database always retrieves a date-time in UTC from any TIMESTAMP WITH TIME ZONE column, you may see otherwise on your receiving end. Such a feature, while well-intentioned, is an unfortunate anti-feature to my mind.

Fortunately, I have not seen any JDBC driver that performs such an adjustment. You should always get a UTC value when retrieving an OffsetDateTime from a TIMESTAMP WITH TIME ZONE column (described below). But test your particular JDBC driver to be sure.

JDBC and the java.time classes

The modern solution for date-time handling in Java is the java.time classes.

To capture the current moment, use Instant. This class represents a moment as seen in UTC.

Instant instant = Instant.now() ;  // Capture the current moment as seen in UTC.

However, despite Instant being the most basic and probably the most commonly used java.time class, the JDBC 4.2 team made the inexplicable decision to not require its support. (Nor is ZonedDateTime support required, by the way.)

Instead, the JDBC 4.2 spec requires support for OffsetDateTime. Fortunately, conversion between Instant and OffsetDateTime is trivial.

OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;

Or you can skip the Instant class is this case.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;

Write to the database.

myPreparedStatement.setObject( … , odt ) ;

Retrieve from the database.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

This OffsetDateTime object will carry an assigned offset from UTC of zero hours-minutes-seconds — commonly called just “UTC” as an abbreviation.

Extract an Instant if needed.

Instant instant = odt.toInstant() ;

Or adjust into a time zone.

ZoneId z = ZoneId.of( "Asia/Tokyo" ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;

A caution regarding possible data-loss: The resolution in java.time is nanoseconds. Capturing the current moment is likely limited to microseconds because today’s commonly-used hardware clocks go no finer. Nevertheless, a java.time object such as OffsetDateTime may carry nanoseconds. Meanwhile, Postgres date-time values have a resolution of microseconds. So if using a java.time object with any nanos present, writing that value with Postgres will truncate the nanos, resulting in a different value to be retrieved later.

I think paragraph 8.5.1.2 of the manual might be enlightening. It states that by default time is assumed to be without timezone and if one is given it is silently ignored.

In order to make things clear I think it is best to explicitely cast the time :

pti=> select timestamp with time zone '20100610T180000-5';
      timestamptz        
------------------------
2010-06-11 01:00:00+02
(1 row)

pti=> select timestamp with time zone '20100610T180000PST';
      timestamptz       
------------------------
 2010-06-11 04:00:00+02
(1 row)

As is evident the time with time zone is properly converted from localtime to server time.

SELECT current_timestamp AT TIME ZONE 'gmt-5';