Eclipse Community Forums: EclipseLink » Unable to retrieve timestampz from postgresql db

Home » Eclipse Projects » EclipseLink » Unable to retrieve timestampz from postgresql db(Eclipselink gives ConversionException retrieving timestamptz field from postgresql database)

Show: Today's Messages :: Show Polls :: Message Navigator

Unable to retrieve timestampz from postgresql db [message #1774480]

Mon, 16 October 2017 08:51

Alberto Brosich

Messages: 2
Registered: October 2017

Junior Member

Hello,
I need to read a timestamptz field of a postgresql (9.6.5) table.

I receive the following error:

[javax.persistence.PersistenceException - Exception [EclipseLink-3005] (Eclipse Persistence Services - 2.7.0.v20170811-d680af5): org.eclipse.persistence.exceptions.ConversionException
Exception Description: Incorrect timestamp format: [2015-07-10 11:53:00+00] (expected [YYYY-MM-DD HH:MM:SS.NNNNNNNNN])]

The entity column is defined as follow:
@Column(name="time")
@Temporal(TemporalType.TIMESTAMP)
private Calendar time;

I'm able to persist but unable to retrieve. Weird...

I tried other java types (e.g. java.sql.Timestamp) without success.
The application run on openjdk version "1.8.0_144".

Any suggest?
Do I have to write a Converter?
Why the behaviour differ between persisting and finding?

Report message to a moderator

Re: Unable to retrieve timestampz from postgresql db [message #1774588 is a reply to message #1774480]

Tue, 17 October 2017 14:56

Chris Delahunt

Messages: 1305
Registered: July 2009

Senior Member

Odd - what is the database column type, and what is the exception stack trace?

Report message to a moderator

Re: Unable to retrieve timestampz from postgresql db [message #1775761 is a reply to message #1774480]

Sun, 05 November 2017 21:49

Alberto Brosich

Messages: 2
Registered: October 2017

Junior Member

Sorry for the late response.

I haven't still found a good solution to deal with postgresql timestamptz fields.
The columns in the database are of type "timestamptz(0)".

The best solution for me would be to use ZonedDateTime fields on java side, but unfortunately it is not supported by JPA 2.2.

I'm using the latest version of Eclipselink (2.7.0), Postgresql JDBC driver (42.1.4), java (1.8.0_151).

I tried Date, Calendar and GregorianCalendar using @Temporal annotation, java.sql.Timestamp and OffsetDateTime,

Is there a best practice for timestamptz?

Regards

Report message to a moderator

Re: Unable to retrieve timestampz from postgresql db [message #1775892 is a reply to message #1775761]

Tue, 07 November 2017 17:17

Rodolfo Fortes

Messages: 2
Registered: November 2017

Junior Member

I guess it's because the timezone.
Try to add this to your field:

@Column(columnDefinition= "TIMESTAMP WITH TIME ZONE")

Report message to a moderator

Re: Unable to retrieve timestampz from postgresql db [message #1779862 is a reply to message #1775892]

Mon, 15 January 2018 03:17

Satya R

Messages: 2
Registered: January 2018

Junior Member

Is anyone able to resolve this? It is working just fine with 2.6.5 or below and I had a converter:

@Column(name = "created_date", nullable = false, updatable = false, columnDefinition="TIMESTAMP WITH TIME ZONE")
@CreatedDate
//@Convert(converter = OffsetDateTimeAttributeConverter.class)
private OffsetDateTime createdDate;

I tried 2.7.1 with converter/without converter and with columnDefinition="TIMESTAMP WITH TIME ZONE" and without it and it is same failure. Interestingly it seems to work fine on my local Mac, but fails on build machine. (Postgresql jdbc driver 42.1.4)

Exception Description: Incorrect timestamp format: [2018-01-15 03:08:11.564173+00] (expected [YYYY-MM-DD HH:MM:SS.NNNNNNNNN]); nested exception is javax.persistence.PersistenceException: Exception [EclipseLink-3005] (Eclipse Persistence Services - 2.7.1.v20171221-bd47e8f): org.eclipse.persistence.exceptions.ConversionException
Exception Description: Incorrect timestamp format: [2018-01-15 03:08:11.564173+00] (expected [YYYY-MM-DD HH:MM:SS.NNNNNNNNN])
at

I am unable to upgrade to 2.7.x due to this. Any pointers would be appreciated.

Report message to a moderator