Date_diff function in LookML

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 :slight_smile:

@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.

2 Likes

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.