BigQuery dimension group datetime datatype time timeframe issues

Looker Q&A Looker
,
1

We have a datetime dimension being read from BigQuery, the problem is on Looker it converts the time value to Zeros.

Is there anyone to retain the time value?

  dimension_group: placed_at_local {
    type: time
    datatype: date
    timeframes: [
      raw,
      hour,
      time,
      date,
    ]
    sql: ${TABLE}.placed_at_local ;;
  }
2

Try and cast the value to a TIMESTAMP @Raghad_Awwad

3

try this and let me know if it works. Mark as helpful if it does. @Raghad_Awwad

  dimension_group: placed_at_local {
    type: time
    datatype: date
    timeframes: [
      raw,
      hour,
      time,
      date,
    ]
    sql: CAST(${TABLE}.placed_at_local AS TIMESTAMP) ;;
  }
4

Unfortunately @Kehinde , it didn’t work. It produced that same error that forced me to define the dimension as a datatime.

5 
  dimension_group: placed_at_local {
    type: time
    datatype: datetime
    timeframes: [
      raw,
      hour,
      time,
      date,
    ]
    sql: CAST(${TABLE}.placed_at_local AS TIMESTAMP) ;;
  }

or 

  dimension_group: placed_at_local {
    type: time
    datatype: timestamp
    timeframes: [
      raw,
      hour,
      time,
      date,
    ]
    sql: CAST(${TABLE}.placed_at_local AS TIMESTAMP) ;;
  }

or

  dimension_group: placed_at_local {
    type: time
    timeframes: [
      raw,
      hour,
      time,
      date,
    ]
    sql: CAST(${TABLE}.placed_at_local AS TIMESTAMP) ;;
  }

I see, you are have a data type mismatch, comparing datetime and date. try the above. (The second third preferably) let me know how it goes. @Raghad_Awwad

1 Like
6

Thank you @Kehinde , the first suggestion worked for me. My mistake was using

datatype: date

which ignored the timestamp

datatype: datetime

corrected that.

1 Like
7

Please mark as helpful. I am happy to help and will always help. @Raghad_Awwad .

1 Like