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

Knowledge Drop

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-form...

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

This content is subject to limited support.                

Version history
Last update:
‎06-14-2021 06:14 PM
Updated by: