Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

UTC Is Enough for Everyone, Right?

zachholman.com

Post reply on HN
Print view View original

121–130 of 321 posts

Re: UTC Is Enough for Everyone, Right?

#121

Post by da_chicken »

jameshart wrote: post #84
da_chicken wrote: post #47

> Properly storing timezone-aware times I'd just like to point out that a lot of RDBMSs support storing date and time alongside timezone information directly without using two separate fields. SQL Server has datetimeoffset, PostgreSQL has timestamp with time zone, and Oracle has timestamp with time zone. I think MySQL does as well, but I seem to recall something strange about it. Or maybe that's just me expecting MyS…

Erp. This sentence made me wince. Times don't have timezones - places have timezones. The idea that time handling is made easier by attaching timezones to times is the cause of so many headaches. Correct time handling involves understanding place - the place where things happen, the place where the user is observing them from, the place where a clock displays a particular time.

Yeah, that's kind of an issue with the ANSI standard. It specifically allows time with time zone.

Some RDBMSs only support datetimes (timestamps) with time zone, but if you want to aim for complete ANSI compliance you're supposed to allow time with time zone. PostgreSQL, which allows that data type, specifically points out in the doc that you shouldn't use it:

> The type `time with time zone` is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of `date`, `time`, `timestamp without time zone`, and `timestamp with time zone` should provide a complete range of date/time functionality required by any application.

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

Re: UTC Is Enough for Everyone, Right?

#122

Post by Terr_ »

0x0 wrote: post #33

So the article seems to imply you should store all timestamps as UTC (with an additional timezone string ID). But for events in the future that needs to happen on a specific "wall clock point in time", it might be better to actually store the yyyy-mm-dd hh:mm:ss as a string with a timezone next to it, because timezones can and do change often unpredictably. If you pre-calculate what "4.00pm next August 1st" is as a U…

IMO the biggest problem isn't the how you store information or even do the date-math. The most frustrating thing is drawing out user-intent, distinguishing between two use-cases which are so similar that most users won't even know what they want.

Specifically, future events which aren't tied to a geographical location, and ones which are. And in the latter case, determining which single location it should be pegged to.

A: "We'll have a global phone call to discuss this crisis in exactly X hours from now."

B: "The keynote for the convention in CityName will occur at 3pm."

Re: UTC Is Enough for Everyone, Right?

#123

Post by [deleted] »

da_chicken wrote: post #117
ssijak wrote: post #60

Earlier quoted context omitted.

That sounds dumb (if column type is called like that text in the quotes)

Why? If you're doing things correctly, you don't need to specify data types particularly often in SQL. You specify it once when the table is created and that's it. All you care about on the programming interface is that your application knows what data type of your language to use with each data column. What's wrong with a verbose and descriptive name for a data type?

[deleted]

Re: UTC Is Enough for Everyone, Right?

#124

Post by da_chicken »

kbrackbill wrote: post #119
da_chicken wrote: post #117

Earlier quoted context omitted.

Why? If you're doing things correctly, you don't need to specify data types particularly often in SQL. You specify it once when the table is created and that's it. All you care about on the programming interface is that your application knows what data type of your language to use with each data column. What's wrong with a verbose and descriptive name for a data type?

I don't think they're saying that having verbose name is dumb, I think it's that a type called "timestamp with time zone" doesn't actually store a time zone.

Yeah, that's what I initially thought, too, but some of the other responses have made me question that.

Re: UTC Is Enough for Everyone, Right?

#125

Post by hrktb »

ivanhoe wrote: post #116
bad_user wrote: post #78

Earlier quoted context omitted.

Actually the meaning of a date like: 2018-05-26T13:45:21+02:00 Will never change, regardless how many time zone changes you may have. What does change is the method to compute the time difference. So can you say how many days, hours, minutes, seconds ago that was from: 2043-02-12T11:15:16+02:00 You won’t be able to, because then you have to account for leap seconds, etc. Also “next Tuesday” does have to take into acc…

The meaning of the date will not change compared to the UTC absolute coordinates, but usually users care only about their local time. If you set an alarm to wake you up at 8:00, you expect it to ring at 8am your time every day. What if in future user's local time zone gets changed - say if daylight savings are abandoned, as many countries consider doing - The user might not be in +2 offset anymore and when you conver…

what the user expect is of course completely different if there’s not one but several users, or several referentials.

Then adapting for local time will screw any schedule not pegged to local time (even for stuff as trivial as following an international event)

Time is hard.

Re: UTC Is Enough for Everyone, Right?

#126

Post by holman »

paulie_a wrote: post #106

The one that has been a problem for me is comparing hourly breakdowns for DST. How do you compare last week vs a day with 25 hours in it

There's a lot of really weird edge cases like that. A lot of the times you sort of have to make a judgement call.

There are cases where you have to make a judgement call, one way or another. Take the case where I invite you to a weekly meeting at 2pm. You live in a place with DST, I don't. DST happens and... is the meeting at 2pm, or 3pm, or what? Google Calendar (and many others) basically say whoever owns the event wins, so our "regular" meeting time would track however you go through life (at least in terms of DST), so times might change for you but not me, and vice versa.

Anyway, yeah, there's some really odd interactions once you start digging deeper.

Re: UTC Is Enough for Everyone, Right?

#128

Post by anothergoogler »

macintux wrote: post #17
holman wrote: post #12

Earlier quoted context omitted.

Ah, totally see where you're going with this. Definitely my bad- I really should have ended this time programming post with a long treatise on hip hop styles and lyricism rather than toss in an obvious joke as a small aside to the longer point. (I am being patronizing here, fwiw.)

You think time has some weird edge cases, but then you get into a discussion about cultural appropriation and you realize how lucky you are to only be worrying about calendars.

I used the word "appropriating" but I wasn't talking about cultural appropriation.

Re: UTC Is Enough for Everyone, Right?

#129

Post by jsd1982 »

da_chicken wrote: post #47

> Properly storing timezone-aware times I'd just like to point out that a lot of RDBMSs support storing date and time alongside timezone information directly without using two separate fields. SQL Server has datetimeoffset, PostgreSQL has timestamp with time zone, and Oracle has timestamp with time zone. I think MySQL does as well, but I seem to recall something strange about it. Or maybe that's just me expecting MyS…

You're confusing time zone with UTC offset. The two are not the same concept. You can determine a UTC offset given a time zone ID and a date/time but the opposite is not necessarily true. A time zone ID cannot be reliably discovered given only its UTC offset; the mapping is not unique.

Re: UTC Is Enough for Everyone, Right?

#130

Post by hyeomans »

Great description of pains I've been feeling the last 5 years in my daily life. Just one comment on colors, I can read the whole post but when I switch or look somewhere else my eyes have a hard time adjusting to all those crazy colors I just saw. Maybe I'm broken.
Post reply on HN