CODEWITHSUNDEEP
javapostgresqltimestamp
Raqib
Raqib

Reputation: 1442

Convert String to Postgres Timestamp and back

Part 1:

I have a file which contains date as string in the following format: 2006-02-16T21:36:32.000+0000

I need to write this value to Postgres in a column which is of type Timestamp. How do I go about doing this in Java?

Part 2:

I need to read the value saved in Part 1 from Postgres, and convert it to a string of the following format "2006-02-16T21:36:32.000+0000"

This is what I have tried:

Timestamp lastSyncDate = Timestamp.valueOf(2006-02-16T21:36:32.000+0000)

When I try to write it to postgres, it gives me the following error:

java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

Upvotes: 0

Views: 5170

Answers (2)

Basil Bourque
Basil Bourque

Reputation: 338181

tl;dr

myPreparedStatement.setObject( 
    … , 
    OffsetDateTime.parse( "2006-02-16T21:36:32.000+0000" )  
)

With and without zone/offset

You said your column is of type TIMESTAMP which is short for TIMESTAMP WITHOUT TIME ZONE. This type purposely lacks any concept of time zone or offset-from-UTC. Not usually appropriate for common business purposes.

You are using wrong type

That is the wrong type for your input. Your input has an offset-from-UTC of zero hours which means UTC itself. Having an offset or zone, your data should only be stored in a TIMESTAMP WITH TIME ZONE type column. In this …WITH… type in Postgres, any submitted offset/zone info is used to adjust into UTC for storage, and then discarded.

Storing a date-time value with an offset or zone in a column of type TIMESTAMP WITHOUT TIME ZONE is like storing a price/cost with a designated currency( USD, Euros, Rubles, etc.) in a column of numeric type. You are losing vital data, the currency. This renders your data worthless.

See the Postgres documentation page for these types.

Smart objects, not dumb strings

Whenever possible, use objects to exchange data with your database rather than passing meter strings. Let your JDBC driver do it’s job in marshaling the data back-and-forth.

Parse the input string as an OffsetDateTime object.

ISO 8601

Your input strings are in a format defined by the ISO 8601 standard. The java.time classes use ISO 8601 formats by default when parsing/generating strings. No need to specify a formatting pattern. 

String input = "2006-02-16T21:36:32.000+0000" ;
OffsetDateTime odt = OffsetDateTime.parse( input ) ;

Define your SQL as a prepared statement.

With JDBC 4.2 and later, you can directly exchange java.time objects with your database. No need to ever again use the troublesome legacy classes such as java.sql.Timestamp.

You could pass an OffsetDateTime. I like to extract an Instant to demonstrate to the reader that I understand how Postgres always stores a TIMESTAMP WITH TIME ZONE value in UTC.

Instant instant = odt.toInstant() ;
myPreparedStatement.setObject( … , instant ) ;

Retrieval.

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

Upvotes: 4

Jasen
Jasen

Reputation: 12392

2006-02-16T21:36:32.000+0000 looks like a ISO-8601 timestamp. postgres understands this format. just treat it like any other string.

don't try to convert it into a java timestamp

Upvotes: 2

Related Questions