event_date and event_timestamp are in UTC. Is that expected?

[velir]

event_date and event_timestamp are in UTC. Is that expected?

[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.