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

Bug story: Sorting by timestamp

adam-p.ca

Post reply on HN
Print view View original

21–30 of 49 posts

Re: Bug story: Sorting by timestamp

#21

Post by arp242 »

gwbas1c wrote: post #11
dotancohen wrote: post #3

Another common reason that time goes backwards is DST. The amount of DST-related bugs that I've fixed over the years amazes me, because every single developer has moved clocks back and forth twice a year for their entire lives, bar a few years in the beginning. And even this fine article mentions the time-has-gone-back possibility yet ignores DST.

> The amount of DST-related bugs that I've fixed over the years amazes me > yet ignores DST Uhm, you do know that you're supposed to use a neutral timezone (Such as UTC) internally in your code / schema, and convert to local time in the UI? The bugs that I've hit generally have to do with using local time accidentally when UTC is expected. > because every single developer has moved clocks back and forth twice a year…

As soon as a time refers to "a time in a specific location" you need to store the location or timezone and can't just rely on UTC. There's a bunch of ways to do that, but "store as UTC, convert to local time in UI" is very simplistic that doesn't always work.

For things like, say, HN posts or whatnot storing as UTC is usually the right answer, but even here there's some nuance; for example email Date: headers tend to include the TZ of the person who sent them, and if you just store all of that as UTC you lose that information.

Re: Bug story: Sorting by timestamp

#22

Post by WirelessGigabit »

Terr_ wrote: post #13
WirelessGigabit wrote: post #5

Sidenote on showing (or not showing) timestamps. Consider a page with 10 rows, and in each row you show the relative date. 2 issues with that: 1) If I see a whole page and I see 1 week ago the range is 7 days. If I see 1 year ago the range is 365 days. That is too much for most of the things. 2) If I am on a page without visual indication of sort order and I'm on a page that shows 10 entries with '1 year ago' I have…

To be pedantic, that's not a problem of relatives times as much as the data being lossy vague approximations. Still, "1 year, 7 months, 2 days, 5 hours, 3 minutes ago" isn't always ideal either, not even when it's done in a lexically sortable way. Ultimately it boils down to a choice which doesn't match problem the user has, and in different circumstances someone might want relative or absolute.

It can be solved by putting in an absolute date. I still don't understand what kind of problem relative dates solved.

Re: Bug story: Sorting by timestamp

#23

Post by pritambaral »

gwbas1c wrote: post #14
loloquwowndueo wrote: post #9

Earlier quoted context omitted.

Every single US developer maybe. In Mexico DST started in 1994 so developers who started before that (hello!) did need some adjustment and operating system support was not a given. There are also countries where DST is not observed or differs from the US yet developers from those countries might be working remotely for a US company, with US developers or needing to accommodate a significant US clientele.

DST is common throughout the world: The days that it starts and stops vary, both by locale, and by year. The operating system keeps track of every locale's DST dates, all the way as long as DST has been a thing. When governments change the date, via law changes, the change usually gets passed along in an OS update.

Reporting from India here. No DST ever. I still know what DST is and how it works, but I've never had to change clocks due to DST.

Re: Bug story: Sorting by timestamp

#24

Post by Terr_ »

WirelessGigabit wrote: post #22
Terr_ wrote: post #13

Earlier quoted context omitted.

To be pedantic, that's not a problem of relatives times as much as the data being lossy vague approximations. Still, "1 year, 7 months, 2 days, 5 hours, 3 minutes ago" isn't always ideal either, not even when it's done in a lexically sortable way. Ultimately it boils down to a choice which doesn't match problem the user has, and in different circumstances someone might want relative or absolute.

It can be solved by putting in an absolute date. I still don't understand what kind of problem relative dates solved.

They work when relative-times are part of the question or mental model the user has when approaching the system.

Then the user doesn't have to mentally cross-convert between relative measures and absolute timestamps, which is less error-prone and annoying.

Re: Bug story: Sorting by timestamp

#25

Post by andreareina »

_kst_ wrote: post #12
dotancohen wrote: post #3

Another common reason that time goes backwards is DST. The amount of DST-related bugs that I've fixed over the years amazes me, because every single developer has moved clocks back and forth twice a year for their entire lives, bar a few years in the beginning. And even this fine article mentions the time-has-gone-back possibility yet ignores DST.

That should only matter if times are stored and sorted as local time. Storing time as UTC (e.g. seconds since 1970) and computing local time from that and the current time zone should avoid DST-related bugs.

Until you deal with the future. If I have a recurring 2 pm meeting, I'd be a little put out if some of those suddenly became 3 pm because of a DST change.

Re: Bug story: Sorting by timestamp

#26

Post by realaleris149 »

aljarry wrote: post #7
dotancohen wrote: post #3

Another common reason that time goes backwards is DST. The amount of DST-related bugs that I've fixed over the years amazes me, because every single developer has moved clocks back and forth twice a year for their entire lives, bar a few years in the beginning. And even this fine article mentions the time-has-gone-back possibility yet ignores DST.

> time goes backwards is DST Only if you deal with timestamps that don't contain timezone information. With TIMESTAMPTZ in postgres it's transparent, you don't have to do anything specifically to manage DST.

Postgres does not store the actual time zone information, it just stores it in UTC. Is a bit counterintuitive but this is how it works [1]. The "with time zone" part is just for parsing and displaying back. When is displayed the value is converted from UTC to the local time in the current timezone which can result in some very interesting discussions :).

[1] https://www.postgresql.org/docs/current/datatype-datetime.ht...

Re: Bug story: Sorting by timestamp

#27

Post by e28eta »

takinola wrote: post #17
e28eta wrote: post #16

Earlier quoted context omitted.

It sounds like you’re ignoring the fact that sometimes programs want to do things based on the user’s current time, and there _are_ reasons for developers to need to be aware of DST and handle edge cases correctly. I inherited some code that was sending push notifications to users with a daily summary. I don’t remember why it was tripping over DST, since the notifications were at a “reasonable” hour (9am?) and afaik…

But using UTC solves that problem, no? The time only changes in DST but not in UTC. If the code checks the time against UST, it will never trip up. Anyways, the only real solution for managing time is to use a library like Luxon so you can stop thinking about it. Time is like cryptographic encryption - Don't roll your own solution if there is a battle tested library available.

It was checking for something like “is it 9am in PST / PDT right now? What about EST / EDT?”

Or maybe, “what’s the current time in this list of time zones and then I’ll send notifications to users where it’s 9am”

Or something, I don’t specifically remember. The point is that notifications aren’t generated until they’re supposed to be sent, and we didn’t want that to vary by 1 hour with daylight savings.

And this specific use case aside, I think it’s true that there’s a class of use cases where developers do need to consider DST.

Re: Bug story: Sorting by timestamp

#29

Post by jupp0r »

I found it to be a generally useful rule to never "ORDER BY created" but instead "ORDER BY created,id" instead to achieve stable sorting.

I recently added some indices to a few tables to speed up a complicated query with lots of subqueries and joins and ran into many unit test failures because usage of the new indices changed the order in which items with the same "created" values were returned.

Re: Bug story: Sorting by timestamp

#30

Post by vivzkestrel »

CREATE INDEX IF NOT EXISTS feed_items_pubdate_id ON public.feed_items USING btree (pubdate DESC NULLS FIRST, id DESC NULLS FIRST) TABLESPACE pg_default;

create an index on both the pubdate (timestamptz column with potential duplicates like your post mentions) and a uuid primary key column (id in my case) problem solved

Post reply on HN