BigQuery PARSE_TIMESTAMP with unix timestamp - Failed to parse input string

Question

I have a table that looks like this

email	created_date
me@you.com	1617753600000
you@me.com	1601510400000

bigquery tells me that created_date is stored a string. So I need to transform created_date from a unix timestamp into a date.

• First I tried PARSE_TIMESTAMP("%s", created_date) but got error: Failed to parse input string "1617753600000"

• Then I tried TIMESTAMP_MICROS(CAST(created_date as int64)) as submitted_at which displays the date correctly Wed Apr 07 2021

I'm curious why does PARSE_TIMESTAMP not work in this case? Isn't this how it should be used?

Answer 1

You have Unix epoch time represented in milliseconds rather than the more common seconds. I recommend using the timestamp_millis() function:

select timestamp_millis(cast(created_date as int64))

Note that there are also functions for timestamp_micros() and timestamp_seconds().

Answer 2

%s - stands for "The number of seconds since 1970-01-01 00:00:00 UTC", but looks like you've got microseconds instead. Try this: parse_timestamp("%s", left(created_date, 10))