Firebase export to BigQuery event timestamp timezone

Question

I have a Firebase that linked to BigQuery to send events data. It creates tables in the old partition way where the date is part of the table name.

Firebase creates a schema that has event_timestamp and event_date. When I look in a partition of specific date the event_date has the same date as the partition but the minimum timestamp starts on 9 pm of the previous date and ends before 9 pm of the partition date.

In the documentation of Firebase schema it is written that event_timestamp is "The time (in microseconds, UTC) at which the event was logged on the client."

So what exactly the timezone of the event_timestamp, the event_date and the date of the partition for Firebase schema?

Is it that the event_timestamp is written in local time?

Answer 1

From what i could understand from the documentation, event_date and the table name follow the timezone of your firebase project configuration; and event_timestamp follows UTC.

When I look in a partition of specific date the event_date has the same date as the partition but the minimum timestamp starts on 9 pm of the previous date and ends before 9 pm of the partition date.

If you query the table using TIMESTAMP_MICROS()

TIMESTAMP_MICROS(event_timestamp) as datetime_utc

The return will contain the timezone info

2021-05-05 14:26:37.704002+00:00

So if you need to use local time, instead of using the timestamp as is cast it to the same timezone of your project and you should be fine.