Hi,
Can you help me understand why I am getting an error with the below date_diff expression in ML? I want to get the days between the date (in the grouped dimension) and today’s date.
dimension: date_diff {
type: number
sql: DATEDIFF(day, ${date_joined_date}, GETDATE()) ;;
}
Thank you!
It depends some on what your underlying SQL language is - we use Impala, and for Impala at least, the datediff()
function only takes two arguments, both dates, and returns the difference.
It looks like this dialect takes a date part, then a start date, then an end date?
Are you sure the outputs for ${date_joined_date}
and GETDATE()
are in the same format?
If you could share the error message that would be helpful as well 🙂
duration
, which allows writing db-agnostic date/time diffs, which I’d say is a better solution.
For example:
dimension_group: since_signup {
type: duration
intervals: [hour, day]
sql_start: ${signup_raw} ;;
sql_end: CURRENT_DATE();;
}
Will show on an explore as “Duration Since Signup”. There are more LookML code samples for duration types on the docs below too.
Hi,
The language is Presto. When I try the duration dimension model, I am still getting an error.
“AthenaJDBC An error has been thrown from the AWS Athena client. SYNTAX_ERROR: line 4:2: Unexpected parameters (varchar(3), varchar, timestamp with time zone) for function date_diff. Expected: date_diff(varchar(x), timestamp, timestamp) , date_diff(varchar(x), date, date) , date_diff(varchar(x), time, time) , date_diff(varchar(x), time with time zone, time with time zone) , date_diff(varchar(x), timestamp with time zone, timestamp with time zone)”
The sql start below is referencing a date timestamp column.
dimension_group: since_signup {
type: duration
intervals: [hour, day]
sql_start: ${dt} ;;
sql_end: current_timestamp;;
}
Using date_diff with my previous dimension(below) gave me the below error as well,
“An error has been thrown from the AWS Athena client. SYNTAX_ERROR: line 4:11: Column ‘day’ cannot be resolved”
dimension: date_diff {
type: number
sql: DATEDIFF(day, ${date_joined_date}, GETDATE())
Thoughts?
Sounds like that syntax isn’t lining up with Athena’s datediff syntax, which is what I think @brecht and @Simon_Ouderkirk were suggesting. Looks like for athena it’s
date_diff('second', min(creation_time),max(ending_time))
Have you tried throwing day in single quotes? Not sure that’d make a difference or not— Also note you’ll want to use date_diff, not DATEDIFF.
Hi! Which is the best way to do a diff days if my database is Druid?
Late reply: Looks like druid has a TIMESTAMP_DIFF function that you could use to get the number of days, if you use DAY for < unit > in that example.
Hi! This worked for me:
date_diff(END DATE, START DATE, day)
I found this documentation very useful for Looker Functions.
https://cloud.google.com/looker/docs/functions-and-operators