CODEWITHSUNDEEP
google-bigquerytimestamp
Iren Ramadhan
Iren Ramadhan

Reputation: 339

How to change string to timestamp?

I have a timestamp data that still in STRING type like below:

+-----------------------------+
| created_at                  |
+-----------------------------+
| 2019-09-05T07:44:32.117283Z |
+-----------------------------+
| 2019-09-05T08:44:32.117213D |
+-----------------------------+
| 2019-09-06T08:44:32.117283A |
+-----------------------------+
| 2019-09-21T09:42:32.117223T |
+-----------------------------+
| 2019-10-21T10:21:14.1174dwC |
+-----------------------------+

How can I change it to ISO Format like "2020-09-05 07:44:32 UTC"?

Thanks in advance

Upvotes: 0

Views: 81

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

You can use PARSE_TIMESTAMP('%FT%T', SPLIT(created_at, '.')[OFFSET(0)]) or PARSE_TIMESTAMP('%FT%T', SUBSTR(created_at, 1, 19)) - whatever you like better

You can test, play with above using sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '2019-09-05T07:44:32.117283Z' created_at UNION ALL
  SELECT '2019-09-05T08:44:32.117213D' UNION ALL
  SELECT '2019-09-06T08:44:32.117283A' UNION ALL
  SELECT '2019-09-21T09:42:32.117223T' UNION ALL
  SELECT '2019-10-21T10:21:14.1174dwC' 
)
SELECT PARSE_TIMESTAMP('%FT%T', SPLIT(created_at, '.')[OFFSET(0)])
FROM `project.dataset.table`

with output

Row f0_  
1   2019-09-05 07:44:32 UTC  
2   2019-09-05 08:44:32 UTC  
3   2019-09-06 08:44:32 UTC  
4   2019-09-21 09:42:32 UTC  
5   2019-10-21 10:21:14 UTC

Upvotes: 1

Related Questions