Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
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 08:51 Go to next message
Alberto Brosich is currently offline Alberto BrosichFriend
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?
Re: Unable to retrieve timestampz from postgresql db [message #1774588 is a reply to message #1774480] Tue, 17 October 2017 14:56 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1305
Registered: July 2009
Senior Member
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 21:49 Go to previous messageGo to next message
Alberto Brosich is currently offline Alberto BrosichFriend
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
Re: Unable to retrieve timestampz from postgresql db [message #1775892 is a reply to message #1775761] Tue, 07 November 2017 17:17 Go to previous messageGo to next message
Rodolfo Fortes is currently offline Rodolfo FortesFriend
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")
Re: Unable to retrieve timestampz from postgresql db [message #1779862 is a reply to message #1775892] Mon, 15 January 2018 03:17 Go to previous messageGo to next message
Satya R is currently offline Satya RFriend
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.
Re: Unable to retrieve timestampz from postgresql db [message #1780611 is a reply to message #1779862] Wed, 24 January 2018 15:21 Go to previous message
Satya R is currently offline Satya RFriend
Messages: 2
Registered: January 2018
Junior Member
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:
  


Current Time: Tue Nov 13 05:04:30 GMT 2018

Powered by FUDForum. Page generated in 0.02236 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top