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)

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

Mon, 16 October 2017 04:51

Eclipse User

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?

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

Tue, 17 October 2017 10:56

Eclipse User

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

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

Sun, 05 November 2017 16:49

Eclipse User

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

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

Tue, 07 November 2017 12:17

Eclipse User

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

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

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

Sun, 14 January 2018 22:17

Eclipse User

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.

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

Wed, 24 January 2018 10:21

Eclipse User

a defect tracking this issue: https://bugs.eclipse.org/bugs/show_bug.cgi?id=522408

Previous Topic:	Multitenant deployment error
Next Topic:	EclipseLink OneToOne CascadeType.All is not working

Goto Forum:

-=] Back to Top [=-

Current Time: Wed Jul 16 23:57:38 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter