How do you handle this string 2023-01-06T00:00:00.000Z when casting as timestamp

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 Solved
0 3 19.3K
1 ACCEPTED 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);;

View solution in original post

3 REPLIES 3

Dawid
Participant V

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.

Top Labels in this Space
Top Solution Authors