[hibernate-dev] 6.0 - formalized standard functions

[Christian Beikov]

Looks good. OTOH I'd also like to see the following functions

  * millisecond_diff
  * second_diff
  * minute_diff
  * hour_diff
  * day_diff
  * week_diff
  * month_diff
  * quater_diff
  * year_diff
  * epoch - generally defined as `extract(epoch from ?1)`
  * group_concat - string aggregation function

The diff functions would return truncated integer values. The function 
subtracts the first from the second argument, converts to the desired 
unit and truncates the result like this "hour_diff('2017-01-01 
01:00:00', '2017-01-02 02:01:01') = 25"
The group_concat function would probably be not be possible to implement 
for all DBMS, but at least the bigger ones have one or the other 
function that could be used to implement this.

Don't know what is best regarding the "str" function. How about 
implementing the function as " ?1 || '' " i.e. concat of the argument 
and an empty string literal. IIRC this operation is kind of 
"context-sensitive" i.e. it will choose a more appropriate text type 
when a type can be inferred.

Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 29.05.2017 um 23:29 schrieb Steve Ebersole:
> Hibernate has always defined a set of functions that should always be
> available on every Dialect/database.  JPA formalized some of these, but
> Hibernate still had a superset.  So I wanted to formalize the complete set
> of supported functions.  Here is the formalized list of what Hibernate has
> always considered the "supported list":
>
>     - those defined by JPA
>     - abs
>        - avg
>        - concat
>        - count
>        - current_date
>        - current_time
>        - current_timestamp
>        - length
>        - locate
>        - lower
>        - max
>        - min
>        - mod
>        - sqrt
>        - substring
>        - sum
>        - trim
>        - upper
>     - Hibernate extensions
>     - bit_length
>        - coalesce - not technically a function, but supported as such to
>        allow overriding
>        - cast
>        - day - generally defined as `extract(day from ?1)`
>        - extract
>        - hour - generally defined as `extract(hour from ?1)`
>        - minute - generally defined as `extract(minute from ?1)`
>        - month - generally defined as `extract(month from ?1)`
>        - nullif - not technically a function, but supported as such to allow
>        overriding
>        - second - generally defined as `extract(second from ?1)`
>        - str - generally defined as `cast(?1 as CHAR )`
>        - year - generally defined as `extract(year from ?1)`
>
>
> Additions/subtractions anyone would like to discuss?
>
> One question I has was in regards to str being defined as `cast(?1 as CHAR
> )` by default - specifically using CHAR instead of VARCHAR.  Some databases
> apply padding for the size of the datatype when casting a numeric to a CHAR
> which is not an unreasonable thing to do.
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev