BigQuery dimension group datetime datatype time timeframe issues

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 ;;
}
2aa38c6e-cc34-444f-b00d-929d9953f3b6.png
Solved Solved
0 6 380
1 ACCEPTED SOLUTION

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

datatype: date

which ignored the timestamp

datatype: datetime

corrected that.

View solution in original post

6 REPLIES 6

Kehinde
Participant II

Try and cast the value to a TIMESTAMP @Raghad_Awwad 

Kehinde
Participant II

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) ;;
}

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

6fd84cb0-e9db-4f41-9fdb-02bbc2804927.png

Kehinde
Participant II
  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 

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

datatype: date

which ignored the timestamp

datatype: datetime

corrected that.

Kehinde
Participant II

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

Top Labels in this Space
Top Solution Authors