Date calcs in RC40.0

Get Help
1

Looking at the latest RC 40.0

Was the date calc functionality described https://github.com/metabase/metabase/issues/11330 implemented in the RC? Cannot seem to see any obvious functions (e.g. datediff, interval etc.) when creating a custom expression.

2

Hi @brianwilson
interval() has always been there:
https://www.metabase.com/docs/latest/users-guide/expressions.html
As for datediff - that is possible by using [date_col1] - [date_col2]
The issue was closed, as I wrote, since it's impossible to handle multiple different requests in a single issue. Please open new requests for single topics, if you are looking for something.

3

thanks @flamber - [date_col1] - [date_col2] seems to return "years between", their doesnt seem to be an obvious mechanism to returns days or months or handle similar timestamp math return mins or secs.

Looking to return as a custom column not use in a filter expression. Will scan github to see whats out there.. thanks again

4

@brianwilson I don't know which database or column types you are querying, but it's returning difference in days for me.

5

using SQLITE (admittedly date implementation is a bit weird with this DB). Tried with columns of type DATE and TIMESTAMP using either returns years - RC40.0

6

@brianwilson Yeah, SQLite's dates are strings and has been the pain of several issues.

7

@flamber I didn't know you could do this date subtraction! The custom expression dialog doesn't suggest a date as the second operand for subtraction for me on v0.39.4 and v0.40.0-rc1, it only suggests number fields. To me this indicated that it's not possible/valid to use it, but if I type the equation manually into the field it works. This is for data from a SQL Server data source.

Also while I'm at it, the result for me is not a datediff() value, but a datetime representing the difference (i.e. time since 1900-01-01, this is SQL Server acting as expected for date subtractions).
e.g. 2021-05-13 00:00 -2021-04-30 00:00 = 1900-01-14 00:00. This would be useful if I could then get any of the dateparts out, not as good as a datediff() but still useful, but I'm not aware of how to do that in Metabase query builder.

datediff() would be so helpful for me.

8

@brianwilson @notrom I have now created an issue for this specifically:
https://github.com/metabase/metabase/issues/16870 - upvote by clicking :+1: on the first post