Daily sales report across timezones: which day does an order at 11:30pm Bangkok belong to?
Lin Wei@linwei
We store created_at as timestamptz. Until now all markets were UTC+8 (Singapore, Malaysia) so we just did:
select (created_at at time zone 'Asia/Singapore')::date as day, sum(amount)
from orders
group by 1;Now we add Thailand (UTC+7). Order at 23:30 Bangkok time is 00:30 next day in Singapore. Thai team want it in their day, regional report want one number per day.
Options: convert each order using its market timezone, or keep one reporting timezone for everything. What do you do in multi-market setup? Any pitfall with daylight saving later if we go to Australia?
Top answer
We do both, explicitly. Store the market timezone on the order (or on a markets table) and compute business_date = (created_at at time zone market_tz)::date. Market reports group by business_date. The regional report uses one fixed reporting timezone and says so in the title. The mistake is mixing both in one chart.
For DST: "at time zone" with a named zone like 'Australia/Sydney' handles it correctly. Never store fixed offsets like +10, they are wrong half the year.
Written by
Lin Wei
Data engineer at e-commerce company, Singapore. dbt, Airflow, Spark, too many dashboards. Trying to make the numbers match between finance and marketing.
Get the best of #select-star-anonymous weekly
One email each Monday with the most useful posts. No spam; unsubscribe in one click.
Comments
Sign in to join the conversation.
Sign in to commentArjun Mehta@arjunmehta
We do both, explicitly. Store the market timezone on the order (or on a markets table) and compute business_date = (created_at at time zone market_tz)::date. Market reports group by business_date. The regional report uses one fixed reporting timezone and says so in the title. The mistake is mixing both in one chart.
For DST: "at time zone" with a named zone like 'Australia/Sydney' handles it correctly. Never store fixed offsets like +10, they are wrong half the year.
Emma Lindqvist@emmalindqvist
Same in energy. Grid data is local time for operations (a day can have 23 or 25 hours with DST, fun) and UTC for anything cross-market. Our rule: never both in one column, and the column name says which one.
Lin Wei@linwei
ok, we will add business_date column computed at insert with the market tz. less surprise for analysts. thanks both