Knowledge Drop

How can I convert "m/d/y hh:mm:ss" to timestamp?

  • 15 June 2021
  • 0 replies
  • 849 views

Userlevel 2
  • Looker Staff
  • 16 replies

Last tested: Nov 12, 2018
 

You may encounter this problem if you have a datetime field string that cannot be used directly into a time dimension_group.

For example, the original data is `7/31/2018 2:20:59`, and you want to convert it into a timestamp `2018-07-31T02:20:59`.

Here's an example how we do it on Bigquery:

SELECT 

PARSE_DATETIME('%m/%e/%Y %k:%M:%S',table1.datetime) AS table1_datetime

FROM `bigquery.schema.table1`

AS table1

GROUP BY 1

ORDER BY 1 DESC

LIMIT 5

 

We used a PARSE_DATETIME() function, and reference each Format Elements individually. The returned value is DATETIME.

In the LookML: we'll make sure we cast the above parse value from datetime to timestamp

dimension_group: datetime {

type: time

timeframes: [second, minute, hour, date, month, raw]

sql: cast(PARSE_DATETIME('%m/%e/%Y %k:%M:%S',table1.datetime) as timestamp);;

}

 

A list of Format Elements in Bigquery: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#supported-format-elements-for-datetime

PARSE_DATETIME() Reference: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#parse_datetime

 

This content is subject to limited support.                

 

 

 


0 replies

Be the first to reply!

Reply