Skip to main content

Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Wrong type returned for aggregate functions over date?(EclipseLink db<>Java type mapping)
Wrong type returned for aggregate functions over date? [message #1856781] Mon, 02 January 2023 08:54 Go to next message
Morten Torp Meiling is currently offline Morten Torp MeilingFriend
Messages: 3
Registered: May 2022
Junior Member

I have a strange problem. I am trying to create a query that returns the MIN of a date field that I have in an @Entity. The field has the Instant java type.

I have an attribute converter in place that handles instants by converting them to LocalDateTime in UTC (since JPA/Eclipselink handles LocalDateTime, and does not support Instants). It should be noted that the dates are saved to an datetime2 column in SQL Server (so it does not have offset information).

This approach works just fine for everything but aggregate functions. So if I have a query that selects MIN or MAX over some field that is mapped to Instant in my entity, the result is a Timestamp and I get a ClassCastException when it tries to use my converter (as that expects a LocalDateTime).

My converter looks as follows:

@Converter(autoApply = true)
public class InstantAttributeConverter implements AttributeConverter<Instant, LocalDateTime> {

	public LocalDateTime convertToDatabaseColumn(Instant instant) {
		return instant == null ? null : LocalDateTime.ofEpochSecond(instant.getEpochSecond(), instant.getNano(), ZoneOffset.UTC);

	public Instant convertToEntityAttribute(LocalDateTime localDateTime) {
		return localDateTime == null ? null : localDateTime.toInstant(ZoneOffset.UTC);

A simplified example of how I create a query using Criteria API
        taskState = root.join(PendingTasksTrigger_.state, JoinType.INNER);
        tasks = taskState.join(TaskState_.tasks, JoinType.INNER);
        criteriaQuery.groupBy(taskState.get(, tasks.get(Task_.creationDate));

So a TaskState has a "tasks" collection that I use in the join, and the deadline is an Instant in the Task entity.

And the error that I get when it tries to invoke my converter:
Caused by: java.lang.ClassCastException: class java.sql.Timestamp cannot be cast to class java.time.LocalDateTime

Re: Wrong type returned for aggregate functions over date? [message #1856782 is a reply to message #1856781] Mon, 02 January 2023 10:23 Go to previous messageGo to next message
Morten Torp Meiling is currently offline Morten Torp MeilingFriend
Messages: 3
Registered: May 2022
Junior Member
A bit embarrassingly i just saw that my only other question is actually about this same issue ( I simply forgot I had already asked this in a slightly different context.
The only reply to the other thread asks why I use LocalDateTime and not Timestamp in the converter. This is because the Timestamp class is quite bad and converts dates in its toString method which results in dates WITH timezone offset being written to the db. The converter I have here solves that...

The reply also asks what version of Eclipselink I use, and I can see that I am actually a bit behind, using only 2.7.8. Upgrading takes a little effort, with JPA 3.0, but I should probably try that anyway...
Re: Wrong type returned for aggregate functions over date? [message #1856864 is a reply to message #1856782] Fri, 06 January 2023 00:43 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 43
Registered: December 2021
I don't know if there are any changes since 2.7.8 that touch on LocalDateTime support, but usually easier to try it out.

What is the database type under the column? I don't understand the problem with toString logic in java.sql.Timestamp - EclipseLink I believe will be converting your data to/from a Timestamp to bind it to the statement, so having an intermediary LocalDateTime conversion seems unnecessary. date/time is just a long value - LocalDateTime is heavily influenced by timezone, and will do conversations from the local Instant 'long' value based on the timezone and why you have to deal with ZoneOffset.UTC in your conversions to/from an instant. Timestamp is just another wrapper over the long value used within Instant, so be less overhead to convert and should be functionally equivalent to what you are doing now.

Best Regards,

[Updated on: Fri, 06 January 2023 00:44]

Report message to a moderator

Previous Topic:spring configuration for eclipselink spring-data jpa
Next Topic:Eclipselink 2.6.9 - Select statements in Logs
Goto Forum:

Current Time: Wed Nov 29 00:18:50 GMT 2023

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

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

Back to the top