14

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!

Yun Zhang
  • 5,185
  • 2
  • 10
  • 29
user3456269
  • 465
  • 2
  • 4
  • 14

3 Answers3

18

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;
user3456269
  • 465
  • 2
  • 4
  • 14
  • Correct link for the function is https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp_seconds – David Mann Aug 31 '21 at 18:19
  • It depends on the source data. Epoch time from Cisco required TIMESTAMP_MILLIS not TIMESTAMP_SECONDS – smoore4 Nov 05 '21 at 20:11
8

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
fire
  • 149
  • 1
  • 5
  • 3
    an even better one is ``` SELECT TIMESTAMP_MICROS(event_timestamp) as converted, event_timestamp, event_date FROM [table] LIMIT 10``` – Andrei Budaes Jul 28 '21 at 09:27
4

Depend on your column values, but in general int64 values consist of 16 digits such as;

- event_timestamp
- user_first_touch_timestamp
- etc...

you should use below functions;

TIMESTAMP_MICROS(event_timestamp) event_datetime    
TIMESTAMP_MICROS(user_first_touch_timestamp) user_first_touch

if it doesn't consist of 16 digits, you can try the other functions which one is suitable for you;

TIMESTAMP_SECONDS
TIMESTAMP_MILLIS
NullException
  • 161
  • 1
  • 5