[deepsun]

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.

[piinbinary]

You can do that with the 'TIMESTAMP WITH TIME ZONE' column type.

https://www.postgresql.org/docs/current/static/datatype-date...

(Edit: or not. See child comment)

[waffle_ss]

Nope, that doesn't store the time zone, it just uses time zone information before flattening to UTC time.

https://stackoverflow.com/a/9576170/215168

[Nullabillity]

When would you want use something else than UTC for business logic? Time zones (and their related nonsense) should be a view-layer concern.

[zAy0LfpBZLC8mAC]

Because 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]

true 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]

For 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

[baddox]

Perhaps because there's not always a one to one relation between a time with zone and a unix time stamp.

[fnord123]

That is correct behaviour. Time zone information is a presentation detail.

[yen223]

Not entirely. Thanks to daylight-savings, you need time zone information to properly calculate lengths of timespans, e.g. for daily recurrences

[fnord123]

Yes, 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.

[lacampbell]

Would it be that much work to add a smallint field, that had the original UTC offset used for your time?

[manigandham]

Timezones are more than just an offset.

[lacampbell]

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.

[0x0]

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.

[baddox]

I 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]

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.

[0x0]

This happens all the time, and sometimes even with just a few days heads-up. Here's a great example: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=457938

[icebraining]

There's always changes happening. Just this year, Southern Chile changed its summer time zone offset, Haiti started observing DST and Mongolia stopped doing that.

[manigandham]

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.

[lacampbell]

Learned something new today! Surprised that never crossed my mind, even though I experience daylight saving every year.

[Nullabillity]

Not really, that still breaks when you have participants from multiple time zones.

[icebraining]

It 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]

No, 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]

Aren'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]

No, 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]

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

[manigandham]

Timezones 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]

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

[hueving]

It does matter for preserving original timezone. An int is a data loss.

[flukus]

While I agree with the other replies, using a smallint would assume all timezones are offset in hourly increments, which isn't the case.