Skip to main content



      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 04:51 Go to next message
Eclipse UserFriend
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 Go to previous messageGo to next message
Eclipse UserFriend
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 Go to previous messageGo to next message
Eclipse UserFriend
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 Go to previous messageGo to next message
Eclipse UserFriend
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 Go to previous messageGo to next message
Eclipse UserFriend
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 Go to previous message
Eclipse UserFriend
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: Wed Jul 16 23:57:38 EDT 2025

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

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

Back to the top