feature request: a function to extract units in interval

[xiangjinwu]

Currently:

extract(minute from interval '1 hour 1 minute') -> 1
extract(epoch from interval '1 hour 1 minute')/60 -> 61

We want:

new_func('minute', interval '1 hour 1 minute') -> 61

Intervals containing year/month should report an error until the desired behavior is clear. In PostgreSQL:

• interval '1 year' = interval '31104000 seconds' is t
• extract(epoch from interval '1 year') is 31557600

Original request:

Is it possible to add to the list date_diff?
Current alternative is taking the epoch and then subtracting by given unit you want:

(EXTRACT(EPOCH FROM date2 - date1) / 86400)::int

Originally posted by @matanper in #8849 (comment)

[xiangjinwu]

The subtraction returns an interval, from which we can extract the number of days directly. SQL Server does not have the interval data type, and defines a set of functions with the 2 steps fused together.

EXTRACT(day FROM date2 - date1)::int

There are several caveats though, given the complexity of date/time:

• It returns the number of whole days, truncating the part less than a day. This is different from SQL Server, which checks specified datepart boundaries crossed.
  For example, DATEDIFF(year, '2005-12-31 23:59:59.999999', '2006-01-01 00:00:00.000000') returns 1 but both workarounds above return 0.
  @matanper Could you confirm which definition of diff/subtract is expected in your case?
• This only works for day but not year/month or hour/minute/....
  • Month: how many months are between 2023-01-31 and 2023-03-01?
    • SQL Server DATETIME returns 2, as explained above.
    • PostgreSQL subtraction (-) returns 29 days, which truncates to 0 months.
    • PostgreSQL age function (not in RisingWave yet) returns 1 mon 1 day.
  • Minute: extract only returns the subfield. extract(minute from interval '1 hour 1 minute') is 1 rather than 61. We still need to extract(epoch) and calculate manually.
• When the input type is date rather than timestamptz or timestamp, the subtraction returns int rather than interval. So we either need extract(day from date2::timestamp - date1)::int, or simply date2 - date1.
  • Do NOT use timestamptz2::date - timestamptz1::date. The cast is timezone-dependent.

[fuyufjh]

cc. @matanper

[matanper]

@xiangjinwu Thanks for the reply.
Right now I'm working with date_diff in Redshift (which is the same as SQL Server), and I'm using only days/hours so I don't have the weird quirks related to month/year.
But I think the more accurate way for me would be to work with intervals and be able to convert it to the given time unit something like extract(minute from interval '1 hour 1 minute') which returns 61

[xxchan]

Would this be a case suitable to be solved by SQL UDF (#10151)?

[fuyufjh]

Is there any reference from other databases/systems?

[KeXiangWang]

Hi, @matanper. (EXTRACT(EPOCH FROM date2 - date1) / 86400)::int could be a feasible trick for your case, currently. We think it is not a too complicated workaround. Since PostgreSQL does not provide a function for date_diff, and we tend to keep risingwave's syntax consistent with PostgreSQL, we've chosen not to make alterations in this regard.

This is our decision for now. Please keep us informed if you face any further issues. We're open to revisiting this decision if needed.