Bigquery - integer to timestamp

Question

I have an integer 20160115201307 and I want to convert it as the timestamp 2016-01-15 20:13:07 UTC. I have tried timestamp(date) but it gave wrong date as 1970-08-22 08:01:55 UTC. Anyone suggest which query I need to use?

Answer 1

That is more likely a string not an integer, and you need to use regular expression to parse the string into components.

SELECT ds, 
   TIMESTAMP(REGEXP_REPLACE(ds, r'(....)(..)(..)(..)(..)(..)', r'\1-\2-\3 \4:\5:\6')) ts
FROM (SELECT '20160115201307 ' ds)

returns

+-----+----------------+-------------------------+--+
| Row | ds             | ts                      |  |
+-----+----------------+-------------------------+--+
| 1   | 20160115201307 | 2016-01-15 20:13:07 UTC |  |
+-----+----------------+-------------------------+--+