CODEWITHSUNDEEP
javapostgresqlpostgresql-9.4jdbc-postgres
din_oops
din_oops

Reputation: 708

Incorrect date fetched from Postgres

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();
        }
   }
}

Upvotes: 1

Views: 7492

Answers (3)

Basil Bourque
Basil Bourque

Reputation: 338564

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.

Upvotes: 4

user330315
user330315

Reputation:

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);

Upvotes: 4

CodeRonin
CodeRonin

Reputation: 2109

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);

Upvotes: 0

Related Questions