1

I have a field called deadline_date of type timestamp with time zone with value 2018-05-03 19:00:00-05.

select deadline_date from invoice_details where file_name='BHDHSB.pdf';

     deadline_date      
------------------------
 2018-05-03 19:00:00-05
(1 row)

But on querying from my java code it is returning the value as 2018-05-04 05:30:00+05:30 .

I think it is converting the the date according to my local timezone.

Is there any way to get the value as stored in the table?

I am using the following maven dependency for Postgres

<dependency>
     <groupId>org.postgresql</groupId>
     <artifactId>postgresql</artifactId>
     <version>9.4.1212</version>
</dependency>

Test.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Test {
    public static Connection connection;
    public void createConnection()
    {
        try {
            if (connection==null) {
                Class.forName("org.postgresql.Driver");
                connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/abc", "abc", "Welcome");
                connection.setSchema("iiv_location");
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
        }
    }

   public static void main(String[] args) {
        try {
            test object = new test();
            object.createConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("select iiv_prescan_invoice_details.deadline_date from iiv_prescan_invoice_details");
            while (resultSet.next()) {
                System.out.println(resultSet.getString("deadline_date"));
           }
        } catch (Exception e) {
            e.printStackTrace();
        }
   }
}
Improve this question
5
  • 1
    Show us your Java code Commented Jul 2, 2018 at 9:26
  • Yes, it would be a Timezone problem... Show us the code... Commented Jul 2, 2018 at 9:29
  • 1
    BTW, the value is the same, just represented in a different timezone... Commented Jul 2, 2018 at 9:45
  • @UsagiMiyamoto yes. the value is same. but my requirement is to get the value as stored in the table. I have added my code in the question. Please check. Commented Jul 2, 2018 at 10:13
  • @a_horse_with_no_name added code. please check Commented Jul 2, 2018 at 10:13

3 Answers 3

Reset to default
4

Do not use getString() to retrieve a timestamp (actually: only use it for strings. Not for numbers, not for dates, not for timestamps).

If you are dealing with timestamps that are time zone aware, you should use a OffsetDateTime in Java.

The Postgres JDBC driver supports this directly through the getObject() method:

OffsetDateTime deadline = resultSet.getObject("deadline_date", OffsetDateTime.class);
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Please Note: This OffsetDateTime and also the LocalTDateTime in another answer is from Java8 / YodaTime only.
No need for OffsetDateTime. Postgres only stores moments in UTC in a column of type TIMESTAMP WITH TIME ZONE, where can simply use Instant in Java. In a column of type TIMESTAMP WITHOUT TIME ZONE, there is no zone or offset at all, not UTC, not anything, in which case use LocalDateTime in Java.
@BasilBourque: that's not entirely true. If you see how e.g. psql displays such a value, it includes the client's time zone. If you want a value that is similar to that, retrieving the timestamp as an OffsetDateTime does make sense to preserve the adjustment that Postgres does when retrieving such a value.
psql is opinionated, taking the liberty to apply that zone adjustment. That is a bad design choice in my opinion, as it creates this confusion, the illusion that Postgres stores with an offset/zone when in fact Postgres stores only in UTC. The Question explicitly asked for the data as it is stored in a column of type TIMESTAMP WITH TIME ZONE. Postgres always stores values in that column in UTC.
@a_horse_with_no_name I had tried the following : OffsetDateTime deadline = resultSet.getObject("deadline_date", OffsetDateTime.class); ,And got date as 2018-05-04T00:00Z But in DB the date is 2018-05-03 19:00:00-05.I need to get the date as in DB.
4

tl;dr

Postgres always stores a moment in UTC in a column of type TIMESTAMP WITH TIME ZONE.

  • Going into the database, adjusted to UTC.
  • Going out, UTC.

Beware of your database access tool: It may alter the retrieved UTC value, applying a zone/offset adjustment. This creates the illusion of the moment having been stored in that zone/offset when in fact it was stored in UTC.

Use objects, not strings, to exchange date-time values with your database.

Use only java.time classes, never the legacy Date/Calendar. For UTC moments, use java.time.Instant class.

Instant instant = Instant.now() ;
myPreparedStatement.setObject( … , instant ) ;

Retrieval.

Instant instant = myResultSet.getObject( … , Instant.class ) ;

Smart objects, not dumb strings

The other Answers are correct in advising you to use objects rather than strings when exchanging date-time values between Java and your database. That is the whole point to JDBC and your JDBC driver, to marshal data back-and-forth while mediating the differences & details.

However those other Answers incorrectly advise the wrong classes to use in Java.

UTC

Postgres saves moments only in UTC. Any time zone or offset-from-UTC information accompanying an input to a database column of type TIMESTAMP WITH TIME ZONE is used to adjust the value into UTC. Then the UTC value is saved to the database, and the original zone/offset forgotten.

Likewise, when retrieving a value from a database column of type TIMESTAMP WITH TIME ZONE, you always get a value in UTC.

However, your middleware or tool being used between you and the database may be choose in alter the UTC value in transit, to apply a time zone or offset, thereby adjusting the value. I consider this to be a poor design choice, though I understand the good intentions behind it. But such behavior creates the illusion that a zone/offset was in the stored data when it was not.

Be aware that I am describing Postgres behavior here. The SQL standard defines the data types I discuss here, but not their behavior. The SQL standard barely touches on the issues of date-time, unfortunately. So various database implementations differ in their behavior. The adjust-to-UTC behavior of Postgres makes sense to me. But note that if you care about a date-time value’s original zone/offset, you must store that yourself in a separate column.

On the Java side, just use Instant for sending and retrieving a moment from a database column of type TIMESTAMP WITH TIME ZONE.

Sending.

Instant instant = Instant.now() ;  // Capture the current moment in UTC.
myPreparedStatement.setObject( … , instant ) ;

Retrieving.

Instant instant = myResultSet.getObject( … , Instant.class );

You can adjust from UTC to some particular time zone.

ZoneId z = ZoneId.of( "Africa/Tunis" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;

If you were working in your business logic with an OffsetDateTime or ZonedDateTime object, extract an Instant by calling their toInstant method.

Instant instant = myZonedDateTime.toInstant() ;  // Extract an `Instant` object, effectively adjusting from some particular time zone to UTC.

Unzoned

An entirely different kind of date-time is one intentionally lacking any concept of time zone or offset-from-UTC. As such, this kind does not represent a specific moment, is not a point on the timeline. This type represents potential moments along a range of about 26-27 hours (the range of various time zones).

For this kind of date-time:

  • In Java, use LocalDateTime class.
  • In Postgres, use TIMESTAMP WITHOUT TIME ZONE type.

When an input is sent to a column of this type in Postgres, any accompanying zone/offset information is ignored. The date and time-of-day are taken as-is and stored directly in the database. When retrieved you get the same date and time-of-day, with no adjustment to any particular time zone.

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.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

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.

Improve this answer

6 Comments

"Going out, UTC" is wrong. It is going out adjusted to the client's time zone.
@a_horse_with_no_name Nope, the value is stored and retrieved in UTC. Your intervening access tool such as psql or database driver may choose to dynamically apply a zone/offset adjustment. Do not conflate your access tool with the database itself. You can avoid this kind of imposed adjustment easily in JDBC by calling myResultSet.getObject( … , Instant.class ) to get the UTC value as it was stored in the database.
It seems we are both right and wrong. If the client and server send data through the binary protocol, then indeed UTC values are sent. But if the client and server send data in text format, the conversion happens on the server. The JDBC driver does not use the binary format by default. Additionally: getObject(...,Instant.class); is (currently) not supported by the Postgres JDBC driver, it results in "conversion to class java.time.Instant from 93 not supported"
@BasilBourque I tried the following : Instant instant = resultSet.getObject( "deadline_date" , Instant.class ); And got this Exception : java.sql.SQLException: unsupported conversion to class java.time.Instant .
@BasilBourque Is there any way to get the time zone name as in DB? I tried show timezone; in the db and got the timezone : US/Central. But on executing the same query in my java code I get my local timezone name.
|
0

you can do it in sql, all you have to do is change your query.

select to_char(iiv_prescan_invoice_details.deadline_date, "yyyy:MM:dd HH24:MI:SS") as deadline_date 
from iiv_prescan_invoice_details

or you can do it in java

LocalDateTime dateTime = resultSet.getTimestamp("deadline_date").toLocalDateTime();
System.out.println("Date Time: " + dateTime);
Improve this answer

1 Comment

LocalDateTime is exactly the wrong class to use for a database column of type TIMESTAMP WITH TIME ZONE. That class lacks any concept of time zone or offset-from-UTC, so it does not represent a moment, and is not a point on the timeline. Use that class for a database column of type TIMESTAMP WITHOUT TIME ZONE. (“without” versus “with”)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.