Menu
I have a column "date_time" in a BigQuery table which contains unix timestamp values like "1569888224". The problem is that these values are integer data types, not timestamp data types, so I do not seem to have an easy way to convert them to human readable date/times. Does anyone have a good way of converting these integers into datetime values in BigQuery?
Thanks!
240
The format is: YYYY-MM-DD HH:MM:SS (e.g. 2021-05-15 16:45:23). Timestamp type: Date, time, and time zone information are all included in timestamps. If no time zone is given, the format falls back to UTC. The format is: YYYY-MM-DD [Timezone] HH:MM:SS (e.g. 2021-05-15 16:45:18 UTC).
Datetime type: comprises both calendar date and time. It does not store time zone information: YYYY-MM-DD HH:MM:SS (e.g. ). Timestamp type: comprises date, time, and time zone information.
The newer "standard SQL" syntax has a set of functions for timestamp operations. That includes timestamp_seconds, which converts unixtime (in seconds - there's another function for milliseconds) to a timestamp: If you want to format that in a particular way, you can convert that to a formatted string with format_timestamp.
Timestamp Functions in Standard SQL. BigQuery supports the following TIMESTAMP functions. NOTE: These functions return a runtime error if overflow occurs; result values are bounded by the defined date and timestamp min/max values.
When casting from string to time, the string must conform to the supported time literal format, and is independent of time zone. If the string expression is invalid or represents a time that is outside of the supported min/max range, then an error is produced. BigQuery supports casting to TIMESTAMP.
The unix time stamp is a way to track time as a running total of seconds. This count starts at the Unix Epoch on January 1st, 1970 at UTC. Therefore, the unix time stamp is merely the number of seconds between a particular
This can be solved by using BigQuery's TIMESTAMP_SECONDS function - https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#format_timestamp
select TIMESTAMP_SECONDS(date_time) from table;
A shortcut to anyone who is trying to convert a Firebase event_timestamp in BigQuery:
SELECT
TIMESTAMP_SECONDS(CAST(CAST(event_timestamp as INT64)/1000000 as INT64)) as converted,
event_timestamp,
event_date
FROM [table] LIMIT 10
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With