Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Solved

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

  • 10 March 2023
  • 3 replies
  • 36 views
DataloopBill

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!

icon

Best answer by DataloopBill 15 March 2023, 12:13

View original

3 replies

Dawid
Userlevel 7
Badge +1

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

 

Dawid
DataloopBill

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

DataloopBill

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.

Reply


Terms of Service