CODEWITHSUNDEEP
java
Killer Beast
Killer Beast

Reputation: 177

Convert java.util.Date to Timestamp with Time Zone for PostgreSQL

I am trying to insert a date into my PostgreSQL db. Here is how I parse the date in my java application:

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
Date parsedTimeStamp = dateFormat.parse(dateString);

And in my PostgreSQL db. I have it as Timestamp with Time Zone. What is the ideal way to process and insert it?

Example of what the db expects 2017-09-09 07:15:33.451061+00.

Upvotes: 3

Views: 14380

Answers (3)

Basil Bourque
Basil Bourque

Reputation: 338171

tl;dr

myPreparedStatement.setObject( 
    … , 
    LocalDate.parse( "20170909" , DateTimeFormatter.BASIC_ISO_DATE )  // Parse string into a date-only object, a `LocalDate` object.
             .atStartOfDay( ZoneId.of( "Asia/Kolkata" ) )             // Apply a time zone to determine the first moment of the day on that date, producing a `ZonedDateTime` object.
             .toInstant()                                             // Extract an `Instant` object, a moment always in UTC. This last step is not technically required, as your JDBC driver with Postgres should effectively do this on your behalf. But this line completes the demo conceptually.
) ;

Avoid legacy classes

The other Answers are outdated, using troublesome old date-time classes that are now legacy, supplanted by the java.time classes.

java.time

With a JDBC driver supporting JDBC 4.2 and later, you can exchange java.time objects with the Postgres server. No need for the java.sql date-time classes, nor the terrible Date and Calendar classes.

Persisting:

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

…and retrieving:

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

In standard SQL, a TIMESTAMP WITH TIME ZONE is a moment, a specific point on the timeline. In other words, a date, a time-of-day, and a time zone. In contrast, you have only a date, lacking the time-of-day and the time zone. You likely want to use the first moment of the day on that particular date in a particular time zone.

To change your date-only value to a moment, first make a LocalDate object.

LocalDate ld = LocalDate.parse( "20170909" , DateTimeFormatter.BASIC_ISO_DATE ) ;

Next, call that LocalDate object’s atStartOfDay method while passing a ZoneId.

A time zone is required to lend meaning to a date. For any given moment, the date varies around the globe by zone. For example, a few minutes after midnight in Paris France is a new day while still “yesterday” in Montréal Québec.

Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 3-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!). 

ZoneId z = ZoneId.of( "America/Montreal" ) ;
ZonedDateTime zdt = ld.atStartOfDay( z ) ;

Finally extract a Instant. 

Instant instant = zdt.toInstant() ;

At this point you can insert that Instant into database as discussed above.

Objects, not strings

Your work will be easier if you learn to think in terms of objects rather than mere strings when exchanging data with a database from Java.

TIMESTAMP WITH TIME ZONE

By the way, be clear on how Postgres uses the data type TIMESTAMP WITH TIME ZONE. The SQL standard defines this data type all too briefly, and databases vary in their implementation. The following describes behavior of Postgres versions 8, 9, and 10.

Any date-time value submitted with a time zone or offset-from-UTC is processed by using that zone/offset to determine a value in UTC. That UTC value is then stored in the database, with a resolution of microseconds (not the milliseconds of legacy Java java.util.Date & Calendar classes, and not the nanoseconds of java.time classes). After the UTC adjustment is made, the zone/offset information is discarded. If you care about the original zone/offset, store that in a separate column explicitly.

When retrieved, the date-time value is sent out from Postgres in UTC. An intervening tool such as psql or pgAdmin may confusingly apply a default time zone. While well-intentioned, such a feature creates the illusion of the stored value carrying a time zone when in fact it does not. In contrast, a JDBC driver compliant with JDBC 4.2 and later will handle the UTC and zone automatically for you. Generally I suggest always retrieving a Instant object as shown in code above. Then apply zones if required to instantiate your own ZonedDateTime or OffsetDateTime objects as shown in code above.

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?

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

GuyKhmel
GuyKhmel

Reputation: 505

SimpleDateFormat you use here translates a string, to a date, in your example, this must mean that the format you use id "yyyyMMdd" - for example 20171217 (today).

To create the string for the DB you need to take the opposite approach. From a date object to a string.

You can create your own formatter for that (using your example)

    Date date = new Date();
    DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss.SSSSSSX");
    String text = formatter.format(date);

You can read more about formatting here

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

From what I saw in the Postgres documentation for JDBC mapping types, Postgres expects a java.sql.Timestamp to be used to map to a timestamp with time zone type. If you were working with a prepared statement, you could use this:

java.sql.Timestamp ts = new Timestamp(parsedTimeStamp.getTime());
PreparedStatement stmt;
// define the statement using Postgres SQL with placeholders
Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
// now set the timestamp at the particular timezone in the statement
stmt.setTimestamp(index, ts, cal);

Replace GMT with whatever timezone you actually want, and replace index with the numerical index of the placeholder where the timestamp should appear in the actual SQL.

Note that more recent drivers which are Java 8 compliant should support using LocalDateTime in addition to java.sql.Timestamp.

Upvotes: 0

Related Questions