After retrieving Timestamp with Timezone from postgres date increases by 1. Why?

Question

I am getting timestamp with timezone value from postgres and in java i am storing the result in Timestamp variable. But the date time changes somehow. I am not getting the original value.

Table

CREATE TABLE log_fail
(
  user_name character varying(99) NOT NULL,
  date_time timestamp with time zone,
  CONSTRAINT pk_log_failed_login PRIMARY KEY (user_name)
)

table data

username = superman

date_time = 2016-12-12 10:06:39.582-08

sql

String uname = "superman";

String sql1 = "select date_time from log_failed_login where user_name = '" + uname + "'";

  rs1 = conn.createStatement().executeQuery(sql1);

  if (rs1.next()) 
        {
  Timestamp attempt_datetime = rs1.getTimestamp("date_time");
        }

Result i am getting

attempt_datetime = 2016-12-12 23:36:39.582

Answer 1

The problem is that the time zone on the machine where your Java code runs is Asia/Calcutta, and the timestamp is converted to that when it is converted to a string.

You can change your time zone in Java like this:

java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone("Europe/Vienna"));

Answer 2

Three offsets

You are not showing us all of your code, so we cannot answer with certainty. But with some deduction I have a theory, a theory of three offsets.

Start with a value eight hours behind UTC → 2016-12-12 10:06:39.582-08

Adjust into UTC → 2016-12-12 18:06:39.582Z

Adjust into offset of +05:30 → 2016-12-12 23:36:39.582+05:30

Awkwardly reported sans offset → 2016-12-12 23:36:39.582

Thanks to the Answer of Laurenz Albe for solving the core issue; I just added a wordy narration here.

A couple of terms:

• An offset-from-UTC is a number of hours and minutes and seconds from UTC, for example +05:00.
• A time zone is an offset plus a set of rules for handling anomalies such as Daylight Saving Time (DST). Properly named as continent/region such as America/Montreal.

Your input 2016-12-12 10:06:39.582-08 represents a moment eight hours behind UTC, used in much of the west coast of North America.

You submitted that value to Postgres for storage. You do not say precisely, but I assume the column in the table is of type TIMESTAMP WITH TIME ZONE.

In Postgres, this type means: “with respect for any accompanying offset/zone info, adjust the incoming value into UTC”. Postgres then discards the accompanying offset/zone info. I repeat, Postgres does not store or remember the offset/zone, not in any of the date-time types. The difference between the WITH type and TIMESTAMP WITHOUT TIME ZONE is that in WITHOUT type any accompanying offset/zone info is ignored entirely, with no adjustments being made.

So when you submitted 2016-12-12 10:06:39.582-08 to Postgres, that value was automatically adjusted to be 2016-12-12 18:06:39.582Z. The Z here is short for Zulu and means UTC. Also, Postgres is not storing these strings literally, instead using its own internal binary storage format. These strings are for us humans to read in this discussion.

Anyways, back to that adjustment to UTC. Note how the time changed from 10 hours to 18 hours, because adjusting from eight hours behind UTC to UTC means adding eight hours.

So, good, the job of storing this value is done. We recorded the exact same moment in time, with 2016-12-12 10:06:39.582-08 and 2016-12-12 18:06:39.582Z both being the very same moment but seen through the lens of two different wall-clock time values. Everything so far is proper and sensible.

Later you retrieved the 2016-12-12 18:06:39.582Z value from the Postgres database. Apparently you extracted the data through JDBC into a java.sql.Timestamp object. Therein lies the problem. This class is one of the old date-time classes bundled with the earliest versions of Java. While laudable as an industry-leading attempt at date-time handling, these classes haven proven to be confusing, troublesome, and flawed. Avoid them whenever possible. They are now legacy, supplanted by the java.time classes.

Amongst the many problems of these legacy date-time classes is the implementation of their toString methods. Thee toString methods are overly-eager to please in implicitly applying the JVM’s current time zone. These has caused no end of confusion to so many Java programmers. Add this Question as another example of this confusion.

First, let's demonstrate this implicit application of time zone. First get the current moment in UTC, as an Instant.

Instant instant = Instant.now ();

Check to see our JVM’s current default time zone and its offset.

ZoneId z = ZoneId.systemDefault ();  // Get current default time zone of this JVM.
String offset = z.getRules ().getOffset ( instant ).toString ();  // Extract the offset-from-UTC from our default time zone.

Lastly, make a java.sql.Timestamp to see the behavior of its toString method.

java.sql.Timestamp ts = new java.sql.Timestamp ( instant.toEpochMilli () );

Dump to console.

System.out.println ( "instant.toString(): " + instant );
System.out.println ( "z.toString(): " + z );
System.out.println ( "offset.toString(): " + offset );
System.out.println ( "ts.toString(): " + ts );

instant.toString(): 2016-12-13T23:06:22.635Z
z.toString(): America/Los_Angeles
offset.toString(): -08:00
ts.toString(): 2016-12-13 15:06:22.635

From this output we can see that the Timestamp is indeed applying my own current default of -08:00 offset to an internal value in UTC. Even worse, the output of this method omits any indication of its offset, leaving us to assume it is UTC when in fact is not. Even more frustrating: this ‘feature’ is entirely undocumented!

Now that we have seen this anti-feature’s behavior in action, we can return to the example data in the Question. Apparently, the resulting data is 2016-12-12 23:36:39.582, lacking any indication of offset as we just noted above. But look at the time-of-day, 23:36:39.582 versus what we might have expected as UTC, 18:06:39.582. The unexpected value is five and a half hours ahead of our expected UTC value, or +05:30. An offset of +05:30 happens to be used in India and Sri Lanka, in zones such as Asia/Kolkata. So we can deduce that the author of this Question was running her code on a JVM with a current default time zone of something like Asia/Colombo or Asia/Kolkata having an offset of +05:30.

Solution

Avoid the legacy date-time classes.

Use java.time classes instead. The java.time classes all have sane simple toString methods that sensibly use the standard ISO 8601 text formats without any unexpected bonus behaviors.

Your JDBC 4.2 compliant driver may be able to directly address java.time types by calling PreparedStatement::setObject and ResultSet::getObject.

myPreparedStatement.setObject( … , instant ) ;

… and …

Instant instant = myResultSet.getObject( … ) ;

If not, fall back to using the java.sql types, but as briefly as possible. Use new conversion methods added to the old classes.

myPreparedStatement.setTimestamp( … , java.sql.Timestamp.from( instant ) ) ;

… and …

Instant instant = myResultSet.getTimestamp( … ).toInstant() ;

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

Where to obtain the java.time classes?

• Java SE 8 and SE 9 and later
• Built-in.
• Part of the standard Java API with a bundled implementation.
• Java 9 adds some minor features and fixes.
• Java SE 6 and SE 7
• Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
• The ThreeTenABP project adapts ThreeTen-Backport (mentioned above) for Android specifically.
• See How to use….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Answer 3

https://www.postgresql.org/docs/current/static/datatype-datetime.html

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone

check what is the timezone of your client:

select setting from pg_settings where name = 'TimeZone';

This will give your an idea, why time differs for different clients