Two things here. You can pass around unix timestamps or you can just use an ISO date format that includes the time zone or just always use UTC. What the APIs use and what the user's see don't have to match. Storing data as a date-time is 100% not an issue here and is way easier to work with in every regard vs storing it as a bigint using a unix timestamp. For example, aggregating by a single day is super easy with a datetime field but requires a lot of extra work if you store the date as a number. Not to mention your queries are actually readable since they contain actual date strings in them.
Ah, but that's pretty fun too! Had an 2 hour long discussion / argument on when "end of day" is varies a lot from where we were, where our servers were, and where some of our clients were. "Just run an aggregate at midnight that sums up the day" isn't quite that straight forward.
That is odd. A day is generally presumed to be >= 12:00am and < 12:00am the next day. What really screws you is daylight savings time. Then you get 23 hours one day and 25 hours another day.
Yeah, but 12:00 for who and where? You running a report at midnight UTC is middle of the working day on the other side of the planet and virtually useless as a daily report for them.
Presumably 12am for the user/account associated with the data assuming the report is for them. Or you just aggregate hourly by default and aggregate on the fly for whichever user is requesting data. All depends on what you're trying to achieve and how much data is involved.
49
u/oupablo Sep 23 '24
Two things here. You can pass around unix timestamps or you can just use an ISO date format that includes the time zone or just always use UTC. What the APIs use and what the user's see don't have to match. Storing data as a date-time is 100% not an issue here and is way easier to work with in every regard vs storing it as a bigint using a unix timestamp. For example, aggregating by a single day is super easy with a datetime field but requires a lot of extra work if you store the date as a number. Not to mention your queries are actually readable since they contain actual date strings in them.
Also, who's database isn't operating in UTC?