Add datetime functions

[lukasj]

There is currently no way to extract parts of a datetime attribute in a standardized way. Hibernate, EclipseLink and DataNucleus have support for that. I propose to add the functions:

• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND

[lukasj]

@glassfishrobot Commented
Reported by c.beikov

[lukasj]

@glassfishrobot Commented
This issue was imported from java.net JIRA JPA_SPEC-102

[lukasj]

@AleksNo Commented
Hi,

+1 to this. And please add:

• WEEKDAY

Thank you.

[lukasj]

• Issue Imported From: https://github.com/javaee/jpa-spec/issues/102
• Original Issue Raised By:@glassfishrobot
• Original Issue Assigned To: @ldemichiel

[m-reza-rahman]

Is this really needed any more with the Java SE date time API? If not, this should probably be closed to reduce clutter.

Reza Rahman
Jakarta EE Ambassador, Author, Blogger, Speaker

Please note views expressed here are my own as an individual community member and do not reflect the views of my employer.

[AleksNo]

Hi,

those functions would be still really nice because they would be usable in the having-clause for filtering data. Currently you have to use native SQL or CriteriaBuilder#function (i guess there is also a JPQL equivalent for this) to do such things, AFAIK. But in both cases you use vendor specific database functions.

Cheers

[beikov]

It sure is needed for various queries, but I think it might be more appropriate to standardize the EXTRACT function like in the SQL standard.

[gavinking]

It sure is needed for various queries, but I think it might be more appropriate to standardize the EXTRACT function like in the SQL standard.

Exactly.

[lukasj]

EXTRACT sounds good to me. Is anyone willing to take ownership of this to make sure we get this into 3.1?

[gavinking]

Well, I was already planning on writing up a proposal for addition of a number of functions to JPAQL, extract() being just one of them. (This was an issue I looked at quite deeply about a year or so ago.)

So I was going to write that up and propose it in an issue this weekend. So assuming we can reach agreement on the precise list of functions that we want to add, I'm very happy to write that up and send a PR to the spec.

[gavinking]

(FTR, extract() is probably the most complex of the functions I would like to see added, since different databases support a rather diverse variety of field types. So perhaps that's something that should still be discussed separately here.)

[beikov]

the precise list functions that we want to add

I guess there are a few that are interesting, but maybe we should split this effort into separate issues so that we can merge certain efforts earlier if we reach agreement on parts?

Regarding temporal functions, I would love to see the following:

• Functions or operator support for arithmetic on temporal and interval/duration types extract(seconds from ts1 - ts2) or ts1 + interval '1 hour'
• Extraction obviously, as stated in this issue extract(quarter from ts1)
• Functions for truncating to a certain precision e.g. TRUNC('hour', timestamp)

[gavinking]

Functions or operator support for arithmetic on temporal and interval/duration types

This is much harder to implement on most databases, and I would prefer to limit ourselves to things that are easy for all vendors to implement at this point.

Functions for truncating to a certain precision

So when I looked into this (quite deeply, as I said) I decided not to include date_trunc() on the list. I don't now recall precisely why not, but I strongly suspect the reason is that it's hard to implement on some databases. According to my list, only Postgres, DB2, Spanner, and Ingres support it natively, though Oracle overloads the trunc() function to do something similar (don't recall if it's equivalent).

I think we should be limiting ourselves (at this early stage) to things that we already know are easy to implement on all major platforms.

[lukasj]

I think we should be limiting ourselves (at this early stage) to things that we already know are easy to implement on all major platforms.

Yes. While it is still unclear what the target date for the platform is going to be - tentatively Fall this year, it would be good to have important stuff in by late August/mid September. So let's focus on the simple parts first to get going and add more later - if there's time or plan them for 3.1.next.

[gavinking]

(FTR, extract() is probably the most complex of the functions I would like to see added, since different databases support a rather diverse variety of field types. So perhaps that's something that should still be discussed separately here.)

So it looks like the set of extract() field types that we know can be made to work across platforms is:

Straightforward:

• year
• quarter
• month
• week
• day
• hour
• minute
• second

Slightly trickier but doable:

• nanosecond

Slightly harder on some platforms, IIRC:

• day of year

• day of month

• day of week

• week of month

• week of year

Easy to implement using typecasts:

• date
• time

We also have some support for extracting offsets/timezones, but that's a morass and JPA shouldn't go there.

Now, JPA surely doesn't need to define all the field types listed above. We need to decide how far to go on this.

[beikov]

I think I would rather see cast(ts2 as Date) than extract(date from ts2), so -0 on the date and time fields from me. +1 on adding the other ones you listed here. Even if some require some work for emulation, I don't think it's too hard. Most major vendors support extraction of these fields directly.

[gavinking]

IIRC, JPA doesn't currently have a cast() function, and I don't think it should be added at this stage, since defining portable semantics for cast() is much harder.

(At some stage, cast() should probably be added, but I'm inclined to think that now is not that time.)

Besides, I feel much more comfortable asking JPAQL to "extract" the time part of a datetime, and trusting that this will do the semantically-correct thing, than asking it to do a typecast and wondering what sort of bodgy semantics that operation might have.

Logically, a datetime comprises a date part and a time part. It makes perfect sense to be able to extract() those pieces.

[beikov]

Sure it makes sense, but what is the type of extract(time from ts2)? LocalTime or java.sql.Time? Is that comparable to fields of a different time type? This is something that a cast could handle in case we define cast in terms of the java type. If this is just the first step towards this, I'm fine with it, but I think in the long term, the cast solution would be more appropriate for extracting whole date and time parts.

[gavinking]

Well, it should be LocalTime, no?

I mean, at this point it I would say we should consider java.sql.Time essentially deprecated, and we shouldn't be using it to define new functionality.

[beikov]

That's fine for me if one can still compare a LocalTime against a java.sql.Time.

[gavinking]

Sure, I don't see why not. I mean, I'm pretty sure that works today. (Though the JPA spec might not say it explicitly.)

[lukasj]

this has been done already

[lukasj]

should anything be missing, file new issue, please