6 ms·
Last time I checked, I couldn't store a datetime _with_ timezone. It was really strange that such a powerful database doesn't support storing full-ISO datetimes
by deepsun 9y ago
Last time I checked, I couldn't store a datetime _with_ timezone. It was really strange that such a powerful database doesn't support storing full-ISO datetimes, like '2017-01-01T00:00:00Z'. Instead, it converted it to date-time-only instant, losing information of original timezone along the way. Sure, I could fetch it back using any timezone I want, but I really wanted to know the original timezone it was in.
- lacampbell 9y agoWould it be that much work to add a smallint field, that had the original UTC offset used for your time?
- manigandham 9y agoTimezones are more than just an offset.
- lacampbell 9y agoTrue. 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.
- 0x0 9y agoAny 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.
- lacampbell 9y agoLearned something new today! Surprised that never crossed my mind, even though I experience daylight saving every year.
- Nullabillity 9y agoNot really, that still breaks when you have participants from multiple time zones.
- icebraining 9y agoIt doesn't "break"; yes, you have to change the local time for the other time zones, but the point is that some times you have to use a specific reference time zone, so you got to save it.
- zAy0LfpBZLC8mAC 9y agoNo, appointments for participants from multiple time zones are not specified in implicit local time, but rather in a some explicit timezone, often UTC, so you have to store that timezone instead of the implicit local one.
- openasocket 9y agoHow 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.
- icebraining 9y agoThere's always changes happening. Just this year, Southern Chile changed its summer time zone offset, Haiti started observing DST and Mongolia stopped doing that.
- openasocket 9y agoSeriously?! Man, dealing with time can just never be simple...
- _jal 9y agoTime, to me, is the canonical example of "things that people think should be easy, aren't". It has everything - complex, constantly changing "business" rules, exceptions generated essentially randomly by a shadowy cabal[1] nobody's ever heard of, "impossible" situations like the 11 days that never happened and other technically arbitrary calendar edits[2], multiple silly base conversions, really weird rules for picking certain dates[3], lots of opportunities for making fencepost errors, and lots of other things. Which leads me to my rule with time programming: Never fail to use a solid library, unless you're unfortunate enough to be writing one. This[4] is a great, necessary but not sufficient book if you have to do that. [1] https://en.wikipedia.org/wiki/International_Earth_Rotation_and_Reference_Systems_Service https://en.wikipedia.org/wiki/International_Earth_Rotation_a... [2] For example, https://en.wikipedia.org/wiki/Swedish_calendar https://en.wikipedia.org/wiki/Swedish_calendar [3] https://en.wikipedia.org/wiki/Easter#Computations https://en.wikipedia.org/wiki/Easter#Computations [4] https://www.amazon.com/Calendrical-Calculations-Nachum-Dershowitz/dp/0521702380 https://www.amazon.com/Calendrical-Calculations-Nachum-Dersh...
- deepsun 9y ago
- deleted 9y ago[deleted]
- baddox 9y agoI don't think that's necessarily true. If you really care about that edge case, then you would need to ask whether the local time should change if the local time zone's UTC unpredictably changes. I don't think you can make a safe assumption either way.
- openasocket 9y agoAren't timezones an offset of UTC by definition? I thought during daylight savings time a country is temporarily changing their timezone. Isn't that why we have EST and EDT: they're two different timezones?
- manigandham 9y agoNo, time zones are a specific geo region that follows a certain time standard. The standard has a relative reference to UTC but that reference may change, either regularly or randomly. Eastern Time Zone is a single time zone, that has different offsets depending on season, formally referred to as EDT and EST to make it easier to identify as daylight or standard references to UTC. Even regions within the same time zone don't follow the standard exactly, so Panama does not observe daylight saving time while New York does. This is why we have even more granular settings used for calendars and dates.
- coldtea 9y agoDoesn'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).
- hueving 9y agoIt does matter for preserving original timezone. An int is a data loss.
- manigandham 9y agoTimezones have more information and are a mini-database in themselves, which is why most database include the information and have timezone capable types. Offsets as simple numbers are not usable in any real calculation. 100% of apps? You must not know what timezone intricacies are then or just how much effort is spent to make sure time itself is properly handled, especially in any major application that has global users.
- coldtea 9y ago>You must not know what timezone intricacies are then or just how much effort is spent to make sure time itself is properly handled, especially in any major application that has global users. You'd be surprised.
- flukus 9y agoWhile I agree with the other replies, using a smallint would assume all timezones are offset in hourly increments, which isn't the case.
- piinbinary 9y agoYou can do that with the 'TIMESTAMP WITH TIME ZONE' column type. https://www.postgresql.org/docs/current/static/datatype-datetime.html https://www.postgresql.org/docs/current/static/datatype-date... (Edit: or not. See child comment)
- waffle_ss 9y agoNope, that doesn't store the time zone, it just uses time zone information before flattening to UTC time. https://stackoverflow.com/a/9576170/215168 https://stackoverflow.com/a/9576170/215168
- deleted 9y ago[deleted]
- Nullabillity 9y agoWhen would you want use something else than UTC for business logic? Time zones (and their related nonsense) should be a view-layer concern.
- baddox 9y agoPerhaps because there's not always a one to one relation between a time with zone and a unix time stamp.
- zAy0LfpBZLC8mAC 9y agoBecause there actually are times that are specified in terms of local time and are not fixed to a specific timezone. Take a birthday, for a trivial example: The span of time in UTC that corresponds to someone's birthday depends on their location at the time.
- talaketu 9y agotrue that anniversaries are not fixed to timezone, but these are not timestamps, and generally not even dates. Just recurrences specified by month, day (or maybe time of day eg Armistice Day observed at 11:00am). Better example: Typically an events schedule should specify destination timezone when registering an event, so that if regulations on local timezone change, scheduled event times remain valid.
- oftenwrong 9y agoFor an example of how storing a UTC datetime for a future event can go wrong, see my comment: https://news.ycombinator.com/item?id=12988092 https://news.ycombinator.com/item?id=12988092
- fnord123 9y agoThat is correct behaviour. Time zone information is a presentation detail.
- yen223 9y agoNot entirely. Thanks to daylight-savings, you need time zone information to properly calculate lengths of timespans, e.g. for daily recurrences
- fnord123 9y agoYes, if you are creating appointments in a calendar program and want to have a daily meeting at 3pm and you are in a jurisdiction with DST, then you will need some TZ info so it knows when to wiggle the time by an hour. But in this case, there's a lot of other information you want to store: first date in the series, repetition frequency, time of the appointment, location (e.g. "Europe/London" as opposed to "GMT" or "UTC+0" which would not be adequate). Basically, you're not so much storing a series of dates as storing a thunk for generating dates.