Hello!
I’ve been working in Looker trying for at least an hour to try to cast this string format (in a table column in Big Query) 2023-01-06T00:00:00.000Z to a timestamp.
I found examples that cover everything but how to handle the “T” and the “Z” in this string. Here’s what I have that is still erroring out:
dimension: date_as_timestamp {
label: "Date to TimeStamp"
type: string
sql: cast(PARSE_DATETIME('%Y-%m-%d %H:%M:%S.%L',${TABLE}.date) as timestamp);;
}
I tried this '%Y-%m-%dT%H:%M:%S.%LZ' and still got an error.
I’ll try this in a second '%Y-%m-%d T %H:%M:%S.%L Z' but don’t expect it to work.
Anybody have an example on how to handle the T and the Z in the string?
Thanks!
Solved! Go to Solution.
Hey all!
I got a reply from Looker Support. This works and I hope it helps others:
For this case we can use this sql parameter in our date dimension, please let me know if it works for you!sql:PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', ${TABLE}.column);;
How about this?
dimension: date {
type: date_time
datatype: timestamp
sql: ${TABLE}.date ;;
}
Alternatively, you can leave type as string if you want the full string
Hey all!
I got a reply from Looker Support. This works and I hope it helps others:
For this case we can use this sql parameter in our date dimension, please let me know if it works for you!sql:PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', ${TABLE}.column);;
How about this?
dimension: date {
type: date_time
datatype: timestamp
sql: ${TABLE}.date ;;
}
Alternatively, you can leave type as string if you want the full string
Thanks Dawid. I appreciate the assist, but that didn’t solve for my specific needs.