51–60 of 128 posts
Earlier quoted context omitted.
True. I'm sure a use case exists where you want to store a timezone, but I don't know what it is. I try and use UTC everywhere, and only worry about time zones when displaying.
Any future event (such as a meeting) can't be stored as UTC, because time zone rules may change between now and the event date, but the event still needs to happen 10am local time.
Earlier quoted context omitted.
Timezones are more than just an offset.
Doesn't matter as far as the DB is concerned. You either just store UTC and co, or you store it just as an datetime + offset. 100% of apps in production don't handle most of the timezone intricacies anyway and the sky hasn't fallen (heck, the sky hasn't even fallen for Y2K).
Earlier quoted context omitted.
How often do time zone rules change? That seems like a fairly rare event. If you're talking about daylight savings time, the date library you're using to convert local time to UTC should account for that.
It doesnt have to be rules, time zones have relative and changing references against UTC, for example daylight saving time which is 2x per year in the US. If you schedule a meeting for 4pm Tuesday next week, but DST happens Friday this week, you still want the meeting on 4pm Tuesday next week. The local timezone and local timestamp is necessary for that.
You cannot have a post entitled "Working with time in Postgres" and fail to mention Range Types[1]! If you're using Postgres right now and have any columns like start_* and end_* for anything (e.g numbers or dates), you need to stop what you are doing and use a range type. They are amazing. You can do things like a unique index that ensures there are no overlapping ranges, you can do efficient inclusion/exclusion ind…
Yes! Range types should absolutely be in here. I had them as one of the top items in a recent post so thought it'd be a bit repetitive[1], but in retrospect, it should absolutely be here as well. [1] http://www.craigkerstiens.com/2017/04/30/why-postgres-five-y...
You cannot have a post entitled "Working with time in Postgres" and fail to mention Range Types[1]! If you're using Postgres right now and have any columns like start_* and end_* for anything (e.g numbers or dates), you need to stop what you are doing and use a range type. They are amazing. You can do things like a unique index that ensures there are no overlapping ranges, you can do efficient inclusion/exclusion ind…
Every time I deviate from using unix milliseconds as my timestamps, I end up regretting it. If we use unix seconds, we get infinite bugs related to people forgetting to convert to millis when comparing against the current time. If we use Date objects, it's an even larger surface of potential bugs. Every Date interface I've ever seen makes it far too easy to accidentally create a relative time (i.e. anything that can'…
Earlier quoted context omitted.
It doesnt have to be rules, time zones have relative and changing references against UTC, for example daylight saving time which is 2x per year in the US. If you schedule a meeting for 4pm Tuesday next week, but DST happens Friday this week, you still want the meeting on 4pm Tuesday next week. The local timezone and local timestamp is necessary for that.
That doesn't sound right. If you have a local timestamp with the date and the time and convert it to UTC, shouldn't the datetime library look up the rules for this timezone and convert it correctly? Isn't that the whole point of having timezones be more than offsets?
If you only store the timezone with a UTC timestamp, when you convert back after DST shifts you'll end up with 3pm or 5pm (depending on the shift), not the expected 4pm for the actual meeting. UTC in this case is not the anchor, the local timestamp the user set for the meeting is.
Earlier quoted context omitted.
It doesnt have to be rules, time zones have relative and changing references against UTC, for example daylight saving time which is 2x per year in the US. If you schedule a meeting for 4pm Tuesday next week, but DST happens Friday this week, you still want the meeting on 4pm Tuesday next week. The local timezone and local timestamp is necessary for that.
If the software converts to UTC it should be using the expected local timezone on the target date (4pm Tuesday), not the current timezone. So this will only be a problem if the TZ tables for the future are not correct (which happens, as noted in sibling comments, but is not as widespread as every DST event like your comment seems to imply).
I'm not sure what this means. Either way, to store an appointment for a certain user, you need the local timezone of that user and the local timestamp. UTC version of that timestamp is optional (maybe for easier calculations in the database), but you must have the local timestamp to convert correctly.
Local TZ => UTC is a formula that is not constant. You cannot store the output of this formula and expect to reverse and get back the original input precisely because of the changing formula.
The week example is a tad misleading, 2017-01-01 is a Sunday, which in some/most? countries is the first day of the week. If the date were 2016-01-01 and you compared it with what week Postgres thinks it is, you'd get: SELECT date_part('week', '2016-01-01'::date); date_part ----------- 53 (1 row) This is because 2016-01-01 is still the 53rd week of 2015. Edit: Actually, 2017-01-01 is week 52 according to Postgres, pr…
The week example is a tad misleading, 2017-01-01 is a Sunday, which in some/most? countries is the first day of the week. If the date were 2016-01-01 and you compared it with what week Postgres thinks it is, you'd get: SELECT date_part('week', '2016-01-01'::date); date_part ----------- 53 (1 row) This is because 2016-01-01 is still the 53rd week of 2015. Edit: Actually, 2017-01-01 is week 52 according to Postgres, pr…
Postgres uses the ISO definition of week for "week", which starts on Monday. For "dow", it uses the American week definition.