Answers inline...
Am 31.05.2017 um 14:38 schrieb Steve Ebersole:
> Thanks for reply Christian..
>
>
> On Wed, May 31, 2017 at 7:18 AM Christian Beikov
> <christian.bei...@gmail.com <mailto:christian.bei...@gmail.com>> wrote:
>
> 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
>
>
> Why not basic temporal arithmetic? E.g. `second(x) - second(y)`
> (which isgenerally resolved to `extract(second from x) -
> extract(second from y)`...
That would only extract the second part of the date/time which is not
what these functions are about. These functions have semantics like
`(epoch(x) - epoch(y)) / unit_factor`. The functions "datediff" from SQL
Server[1] or "timestampdiff" from MySQL[2] have exactly these semantics.
To give an example, consider the following expression
second_diff(
'2017-01-01 00:01:01'::timestamp,
'2017-01-01 00:00:01'::timestamp
)
When defining second_diff(x, y) as `second(y) - second(x)`, the result
of that expression would be 0.
The semantics I anticipate are that this returns 60 which is what could
be implemented by doing `(epoch(y) - epoch(x)) / unit_factor` where
unit_factor for seconds would be simply 1.
This is not that easy to implement correctly(I have implementations for
the most common DBMS), although I think these are quite commonly needed
functions.
> * epoch - generally defined as `extract(epoch from ?1)`
>
>
> Because ANSI SQL does not define epoch as an extractable part of a
> temporal. TBH I do not think it defines milliseconds as extractable
> either but would have to check. Also, I am not sure of databases that
> support that, nor any alternate (which we'd *need* to support this as
> a standard func) for databases which do not.
I have implementations for the most common DBMS that use the proprietary
counterparts or calculate the epoch appropriately. Although I don't
think there are that many DBMS that don't support this in any way, the
question is if we ought to "not implement the function" because e.g.
SQLite can't do it. I think one of the main questions we should ask
ourselves is for which DBMS we'd like to provide support for these
stanard functions.
>
> * group_concat - string aggregation function
>
>
> How is this different from concat()?
>
> 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.
>
>
> Unless I am missing something in your idea here (which is why I asked
> above) we can absolutely implement this on all databases - everyone
> has a concat operator as well. And a SQM/SQL-AST function need not
> evaluate to a db function - it just needs to be an expression.
group_concat is an aggregate and/or window function like count, sum, avg
etc. which aggregates text values. You can think of group_concat as
being the StringJoiner of SQL. It allows to do e.g.
select order.id, group_concat( concat ( i.amount, 'x ', i.product.name
), ', ' )
from Order o
left join o.items i
group by order.id
Assume you have data like
Order( id = 1, items = { Item( amount = 2, product.name = 'Beer' ),
Item( amount = 1, product.name = 'Burger' ) } )
this would produce 1 row [1, '2x Beer, 1x Burger']
This was just a simple example to demonstrate the purpose, but in
reality the function must also be able to have an order by clause. MySQL
defines group_concat([distinct] expression ORDER BY expression1
[,expressionN] [SEPARATOR sep]).
Other DBMS have similar functions like listagg, string_agg etc. that all
have that same functionality.
> Don't know what is best regarding the "str" function.
>
>
> `str()` is defined as `cast`; that's not the issue. The issue is
> merely the type - I am simply arguing that VARCHAR is more correct
VARCHAR as type sounds ok to me, but I don't think I ever used it.
Whenever I need conversion I do so to concat the value with some other
string so I use the concat operation without "str".
[1]
https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql
[2]
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff
_______________________________________________
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
