View the original community article here
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.