CODEWITHSUNDEEP
javapostgresqldatejava.time.instant
LechP
LechP

Reputation: 851

postgresql Date vs java Instant

Suppose I have a field in Java app of type Instant and store it in PostgreSQL database as type DATE.

If I save for example 21.02.2019 14:03:59 it will be stored as 21.02.2019 but how will it be re-translated when I read from database?

Will it be:

PS. I know I should use Timestamp instead :)

Upvotes: 1

Views: 5305

Answers (2)

Basil Bourque
Basil Bourque

Reputation: 338171

tl;dr

  • A DATE column in a SQL-standard-compliant database such as Postgres stores only a date, no time and no zone.
  • Use LocalDate for a date-only value, without time and zone.
  • To get a LocalDate from an Instant, specify the wall-clock time used by the people of a particular region (a time zone) through which you want to determine a date.
  • If you want to store a moment (as represented by an Instant object), use the SQL-standard type TIMESTAMP WITH TIME ZONE rather than DATE.

Details

Instant is in UTC

An Instant object represents a moment in UTC, always in UTC. In other words, a moment with an offset-from-UTC of zero hours-minutes-seconds.

Determining date requires time zone

Getting a date from an Instant therefore requires a time zone (a ZoneId). A moment after midnight in Paris France is a new date, say the 23rd, while in Montréal Québec it is still “yesterday” the 22nd. For any given moment, the date varies around the globe by zone.

So if you meant the date as seen in Québec, specify the time zone `America/Montreal".

ZoneId z = ZoneId.of( "America/Montreal" ) ;
ZonedDateTime zdt = instant.atZone( z ) ;

You said your database column is of type DATE. In Postgres, like the SQL standard, that means a date-only value, without a time-of-day and without a time zone.

To store only the date, without a time-of-day and without a time zone, extract a LocalDate. The word “local” means any locality or all localities, but not any one particular locality.

LocalDate ld = zdt.toLocalDate() ;

JDBC 4.2

Exchange that LocalDate via a placeholder in a prepared statement.

myPreparedStatement.setObject( … , ld ) ;

Retrieval:

LocalDate ld = myResultSet.getObject( … , LocalDate.class ) ;

Date stored, date retrieved

how will it be re-translated when I read from database?

Will it be:

21.02.2019 00:00:00.000 or …

This question makes no sense. When you read a DATE from a SQL-standard compliant database like Postgres, you get a date, only a date — no time-of-day, and no time zone.

If you store a LocalDate of value 2019-02-21 in a DATE column, you get back the same: a LocalDate of value 2019-02-21.

First moment of the day

If you want to add a time-of-day for the first moment of the day, let java.time determine that for you. On some dates in some time zones, the day does not begin at 00:00:00 but may instead start at a time like 01:00:00.

LocalDate ld = myResultSet.getObject( … , LocalDate.class ) ;
ZoneId z = ZoneId.of( "Pacific/Auckland" ) ;
ZonedDateTime zdt = ld.atStartOfDay( z ) ;  // Returns a `ZonedDateTime` object set to the first moment of the day as seen on that date in that time zone. May or may not be the time 00:00:00.

UTC

If you want to extract a date from your Instant using UTC as your time zone, meaning an offset-from-UTC of zero hours-minutes-seconds, use the OffsetDateTime class with the constant ZoneOffset.UTC.

OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;
LocalDate ld = odt.toLocalDate() ;
myPreparedStatement.setObject( … , ld ) ;

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.

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

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

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: 6

user11096416
user11096416

Reputation: 21

That depends on how you're reading it. Instant and SQL DATE are not equivalent, and I don't know any reliable library that would even allow you to read DATE to Instant. If you write your own code that allows this, then you can make it fill the hour, minute, second and milliseconds in any way you want to, even if it doesn't make sense (so the real answer to your question would be "neither" unless you explicitly write code to transform java.sql.Date to java.time.Instant).

If you're designing software that stores instants as dates, then your design is flawed. Either use LocalDate on Java side, or TIMESTAMP in the database.

Upvotes: 2

Related Questions