Converting string column to timestamp in BigQuery?

Question

I am trying to convert a column in a BQ table to timestamp. I have two string columns, one for utc_hour (string 0-23) and utc_day (string yyyymmdd) imported from a public data source. I merged the two columns to produce a string column, utc_timestamp, with strings like this - "20171208 500" .

I need to convert that string into timestamp, and when I use

TIMESTAMP(utc_timestamp)

I get the error message

Invalid timestamp: '20171208 500'

I tried using dataprep, which also could not convert that string to a timestamp.

How can I convert this format to a timestamp?

Answer 1

Try to parse with %Y%m%d%k%M format.

PARSE_TIMESTAMP("%Y%m%d%k%M", utc_timestamp)

Answer 2

This question is already answered but in case someone else visits here with his/her own unique timestamp format (in which case the accepted answer might not work), you need to follow the notations on this documentation page

For eg, in my case values were like this 2020-05-11-00:00:00. So, I went to the above-mentioned page and found that the format string that I needed would be something like %Y-%m-%d-%H:%M:%S.

I will put the description for the ones that I used in my format string:

%d  The day of the month as a decimal number (01-31).
%H  The hour (24-hour clock) as a decimal number (00-23).
%M  The minute as a decimal number (00-59).
%m  The month as a decimal number (01-12).
%S  The second as a decimal number (00-60).
%Y  The year with century as a decimal number.

The :s and the -s are quite self-explanatory.