event_date and event_timestamp are in UTC. Is that expected?

[adamribaudo-velir]

As pointed out here, the event_timestamp field is in UTC: https://support.google.com/analytics/answer/7029846?hl=en#zippy=%2Cevent

I assume the event_date field is as well. My preference would be to convert these to the relevant reporting time zone as early in the pipeline as possible. Otherwise, users will need to handle this conversion in the BI layer which I'm certain will be overlooked frequently.

I propose a 'timezone' var that can be used to convert the event_timestamp and generate a new event_date_dt field in the base layer. (bigquery uses time zone names located here: http://www.iana.org/time-zones )

Curious to get input from others.

[willbryant]

Pretty sure I found the table date suffixes and event_date_dt were actually the same timezone you chose for reporting GA4 settings, which was a pleasant surprise.

[adamribaudo-velir]

Can you elaborate on how you verified that? The documentation says UTC 🤔 Get Outlook for Android<https://aka.ms/AAb9ysg>

…

________________________________ From: Will Bryant ***@***.***> Sent: Saturday, March 4, 2023 3:42:23 PM To: Velir/dbt-ga4 ***@***.***> Cc: Adam Ribaudo ***@***.***>; Author ***@***.***> Subject: Re: [Velir/dbt-ga4] event_date and event_timestamp are in UTC. Is that expected? (Issue #144) Pretty sure I found the table date suffixes and event_date_dt were actually the same timezone you chose for reporting GA4 settings, which was a pleasant surprise. — Reply to this email directly, view it on GitHub<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FVelir%2Fdbt-ga4%2Fissues%2F144%23issuecomment-1454869167&data=05%7C01%7Cadam.ribaudo%40velir.com%7C3162c8fe0c2244c381bf08db1cf0f5e5%7C750f89c4532d43399fd9b605eca241c8%7C1%7C0%7C638135593476351337%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=M2CQbJFjGgOFLTMmiX2fOAo2YQjkVk3iSvURULlxZZ4%3D&reserved=0>, or unsubscribe<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FARCCAAACEUDIE5Y4NPSOEQLW2OSK7ANCNFSM6AAAAAAVPXMDU4&data=05%7C01%7Cadam.ribaudo%40velir.com%7C3162c8fe0c2244c381bf08db1cf0f5e5%7C750f89c4532d43399fd9b605eca241c8%7C1%7C0%7C638135593476351337%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=AtBXRuNki7Ez8Jqv5%2B6%2B8DlS0Jv%2B4nSAYOK7UmOHBxI%3D&reserved=0>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[willbryant]

Huh. Ok, I’ll have a look at work tomorrow.

[adamribaudo-velir]

ok I'm with you now, just ran this and I get the exact same event counts per day

SELECT 
  DATE(TIMESTAMP_MICROS(event_timestamp) , "America/Los_Angeles") event_date, 
  count(event_timestamp) as event_count 
FROM `*****.events_*` 
where DATE(TIMESTAMP_MICROS(event_timestamp) , "America/Los_Angeles") >= '2023-02-01'
group by 1
order by event_date asc

I'll close this issue and add a comment in the .yml docs that event_timestamp & event_date are set to the property time zone

[willbryant]

Just to clarify, event_timestamp is documented as being in UTC because the bigquery TIMESTAMP type is an universal "instant of time" value, which is shown in UTC in query results unless you ask for something else.

But for event_date_dt, DATE values aren't a universal "instant of time", so they need to be in a defined timezone.

So, event_timestamp should be documented as UTC as people will in practice need to use a query like the one you used to see it in their own timezone. They can convert to a DATETIME if they want that local TZ representation instead.