How to using BigQuery PARSE_TIMESTAMP parse DateTime concat with underscore?

Question

I have a time stemp formated as a string like this: 2022_05_26_13_52_05 which is format as YYYY_MM_DD_hh_mm_ss how can I use BigQuery PARSE_TIMESTAMP to parse it? I try it with

SELECT PARSE_TIMESTAMP("%Y_%m_%d_%I_%M_%S", "2022_05_26_13_52_05") from `Something.test.my_table_name_*`

It fails at: Failed to parse input string "2022_05_26_13_52_05", but if I only parse PARSE_TIMESTAMP("%Y_%m_%d", "2022_05_26"). It works perfectly fine. Many thanks!

Mikhail Berlyant · Accepted Answer · 2022-06-08 18:18:32Z

2

try below instead

PARSE_TIMESTAMP("%Y_%m_%d_%H_%M_%S", "2022_05_26_13_52_05")

with result

2022-05-26 13:52:05 UTC

answered Jun 8, 2022 at 18:18

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zichen Ma Over a year ago

Hi Mikhail, it works perfectly fine. Looks like, I put %I for wrong hour format. Thanks a lot

Mikhail Berlyant Over a year ago

sure. yes - this was the only wrong piece :o)

Collectives™ on Stack Overflow

How to using BigQuery PARSE_TIMESTAMP parse DateTime concat with underscore?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related