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?

Solution 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 |  |
+-----+----------------+-------------------------+--+

Solution 2

With standard SQL:

SELECT PARSE_TIMESTAMP('%Y%m%d%H%M%S', CAST(20160115201307 AS STRING))

With 900 records in a table:

SELECT PARSE_TIMESTAMP('%Y%m%d%H%M%S', CAST(col AS STRING))
FROM `dataset.table`

To enable standard SQL:

• https://cloud.google.com/bigquery/sql-reference/enabling-standard-sql

Comments

[nikhil]

I have 900 records in a table.how to apply this for all records?

[Pentium10]

You can run a query that does the transformation and write it back to the same table, you can see under the options in the BQ WebUI that you can specify the same tables destination. Make sure you include all your columns you need.

[nikhil]

So,do i need to perform this query for 900 times?

[nikhil]

Thanks for the help!!But I am getting the error "Failed to Parse string "0"" when i run query...

[Pentium10]

No, the purpose of SQL language is to write the query that does that. You need to adapt the solution which we showed you to run it on your schema.

[nikhil]

I have some timestamp fields named as 0 in some records.

[Elliott Brossard]

Does a value of 0 mean NULL? In the cast you could instead do CAST(IF(col = 0, NULL, col) AS STRING).

[nikhil]

i am attaching screenshot for this issue.prntscr.com/cfwbkz