Date_diff errors

datediff

#1

Hi, I am having problem with the date_diff solution for dev:

I currently have:

  dimension_group: ts_1 {
    type: time
    timeframes: [date, raw]
    sql: ${TABLE}.ts_1 ;;
  }

  dimension_group: ts_2 {
    type: time
    timeframes: [date, raw]
    sql: ${TABLE}.ts_2 ;;
  }

  dimension: date_diff {
    type: number
    sql: DATEDIFF(day, ${ts_2}, ${ts_1}) ;;
  }

and am receiving an error message in the UI.

Any solutions? Thanks!


(leticia.esparza) #2

Hey @dancingo,

I hope all is well! For the dimension executing the DATEDIFF, it looks like we are currently referencing the two dimension groups above it instead of referencing the explicit fields we are trying to subtract. What I mean by this is, we have something like:

DATEDIFF(day, ${dimension_group_2}, ${dimension_group_1}) 

when we should be subtracting between two date fields or two timestamp fields. So we should have something more like:

DATEDIFF(day, ${date_2}, ${date_1}) 

Dimension groups are essentially groups of dimensions so if we want to reference a field within each dimension group, we can do this by appending the timeframe to the end of the dimension group name, as mentioned here. So if we want to reference date from ts_1, we would reference it like so: ts_1_date. If we want to reference raw from ts_1, we would append _raw like ts_1_raw.

Let’s try referencing the fields within each dimension group in the sql of the date_diff field instead of referencing the dimensions groups themselves. It will look something like:

dimension: date_diff {
type: number
sql: DATEDIFF(day, ${ts_2_date}, ${ts_1_date}) ;;
}

I hope this helps! Let me know if I can clarify anything about this on my end!

Best,

Leticia