Date_diff function in LookML

Punita · 06-18-2019 05:22 AM

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!

Simon_Ouderkirk

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 🙂

brecht

@Simon is right - this is very database-specific, your db reference should have the correct SQL syntax. LookML does however have a dimension type of 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.

Punita

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?

izzymiller

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.

bryan_rubi

Hi! Which is the best way to do a diff days if my database is Druid?

izzymiller

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.

lorema

Hi! This worked for me:

date_diff(END DATE, START DATE, day)

Kunisetty

I found this documentation very useful for Looker Functions.
https://cloud.google.com/looker/docs/functions-and-operators