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

41–49 of 49 posts

Re: Bug story: Sorting by timestamp

#41

Post by takinola »

e28eta wrote: post #27
takinola wrote: post #17

Earlier quoted context omitted.

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 spec…

I have no doubt that this may have been some crazy complicated case (as dealing with time can be) but I can't help the armchair quarterback debugging. Could this not have been addressed by periodically running a script that generated when (in UTC) a notification should be sent and then another script to send the notifications when that time comes around ie

Script 1: send notification to User 123 at 9:00 UTC

Script 2: It is 9:00 UTC, which users are due to have notifications that have not been sent?

This way, the timezones don't ever really come into play. The axiom I follow for dealing with time is collect and display time in user's local time but store and process in UTC.

Re: Bug story: Sorting by timestamp

#42

Post by e28eta »

takinola wrote: post #41
e28eta wrote: post #27

Earlier quoted context omitted.

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 spec…

I have no doubt that this may have been some crazy complicated case (as dealing with time can be) but I can't help the armchair quarterback debugging. Could this not have been addressed by periodically running a script that generated when (in UTC) a notification should be sent and then another script to send the notifications when that time comes around ie Script 1: send notification to User 123 at 9:00 UTC Script 2:…

If you choose to divide it like that, script 1 is the one that needs to know about DST, because user 123 might be 9 UTC today, but what is it tomorrow?

Might change due to a DST change (which varies by time zone, and time zone DST date can change year-to-year), or the user might change time zones. Either way, your Script 1 is the logic that had the bug I inherited.

Re: Bug story: Sorting by timestamp

#43

Post by adam-p »

jupp0r wrote: post #29

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.

Nice. That's a good general rule to follow.

Re: Bug story: Sorting by timestamp

#45

Post by zoky »

keep_reading wrote: post #44
zoky wrote: post #32

Earlier quoted context omitted.

Won’t work if your ID is a UUID. Also, this is more generally applicable to any date, not just created.

UUIDv7 is sortable by time

And I’m sure that will be incredibly useful in 20 years when UUIDv7 has entirely supplanted UUIDv4 in all legacy systems (and you only need to sort by date created), but for now let’s just go with the best practice for the foreseeable future and sort in a way that not only ensures consistency but future-proofs us indefinitely.

Re: Bug story: Sorting by timestamp

#46

Post by out_of_protocol »

growse wrote: post #40
out_of_protocol wrote: post #20

Earlier quoted context omitted.

Well, when it's not time, just some numbers. The most you can do is to store as is, hoping to figure out what that means later. Also, even if all inputs and outputs are in local time, it still makes sense to process everything in UTC. There are a lot of weird corner cases making math on datetime really complicated

This doesn't work reliably for future timestamps. If a user submits a local timestamp and you then convert it to UTC, you're using the current definition of what we think that offset will be at that future time. However, timezone definitions change. By doing the conversion, you've got to remember to check that the offset between what you stored and the user's local hasn't changed since the time you did the conversion…

UTC or unix timestamp gets you moment of time. You know exactly moment event happened. Definitions can change, but usually not retroactively. TZdata is a database of such changes, and stores definition changes, basically all *nix OSes depend on it to cast moment of time into timezone. You can always convert timestamp in the past into desired timezone. If you work with calendars and want you know what "04:00 tomorrow" means - good luck, you'll need it. as example, "04:00 tomorrow" may not exist at all

Re: Bug story: Sorting by timestamp

#47

Post by out_of_protocol »

out_of_protocol wrote: post #46
growse wrote: post #40

Earlier quoted context omitted.

This doesn't work reliably for future timestamps. If a user submits a local timestamp and you then convert it to UTC, you're using the current definition of what we think that offset will be at that future time. However, timezone definitions change. By doing the conversion, you've got to remember to check that the offset between what you stored and the user's local hasn't changed since the time you did the conversion…

UTC or unix timestamp gets you moment of time . You know exactly moment event happened. Definitions can change, but usually not retroactively. TZdata is a database of such changes, and stores definition changes, basically all *nix OSes depend on it to cast moment of time into timezone. You can always convert timestamp in the past into desired timezone. If you work with calendars and want you know what "04:00 tomorrow…

* ignoring leap seconds

Re: Bug story: Sorting by timestamp

#48

Post by adam-p »

vivzkestrel wrote: post #30

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

There are a couple of shortcomings that I think I see:

1. If the clock moves backwards (and an "older" record gets created), it doesn't help.

2. If a duplicate timestamp is created with an id that sorts earlier than one of the other dups (and a user has already synced to the first dup).

In both of those cases, the user won't receive the new record. (Yes, neither is probable, but neither is impossible.)

Re: Bug story: Sorting by timestamp

#49

Post by keep_reading »

zoky wrote: post #45
keep_reading wrote: post #44

Earlier quoted context omitted.

UUIDv7 is sortable by time

And I’m sure that will be incredibly useful in 20 years when UUIDv7 has entirely supplanted UUIDv4 in all legacy systems (and you only need to sort by date created), but for now let’s just go with the best practice for the foreseeable future and sort in a way that not only ensures consistency but future-proofs us indefinitely.

Then don't use UUIDs, use snowflakes / flakes
Post reply on HN