Bug story: Sorting by timestamp

#1

Post by adam-p » Sat, Dec 23, 2023, 4:53 PM UTC

Bug story: Sorting by timestamp
adam-p.ca

Re: Bug story: Sorting by timestamp

#2

Post by senderista » Sat, Dec 23, 2023, 8:09 PM UTC

Timestamp resolution doesn't just depend on the storage granularity of the timestamp type; it also depends on the actual accuracy of whatever system call is used to populate the timestamp.

Re: Bug story: Sorting by timestamp

#3

Post by dotancohen » Sat, Dec 23, 2023, 8:14 PM UTC

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.

Re: Bug story: Sorting by timestamp

#4

Post by WirelessGigabit » Sat, Dec 23, 2023, 8:22 PM UTC

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.

Oh the joys of living in Arizona! Not having to deal with confusion twice per year! Not having to deal with these sort issues!

Or so I thought.

All calendar invites I own are set in Arizona time, so for everybody else they shift. Queue the rescheduling requests.

For the ones I don't own it actually is better for me as they all shift an hour later.

Re: Bug story: Sorting by timestamp

#5

Post by WirelessGigabit » Sat, Dec 23, 2023, 8:29 PM UTC

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 no clue about the sort order.

I hate relative dates.

Re: Bug story: Sorting by timestamp

#6

Post by [deleted] » Sat, Dec 23, 2023, 9:16 PM UTC

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…

[deleted]

Re: Bug story: Sorting by timestamp

#7

Post by aljarry » Sat, Dec 23, 2023, 9:36 PM UTC

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.

Re: Bug story: Sorting by timestamp

#8

Post by aljarry » Sat, Dec 23, 2023, 9:41 PM UTC

Interesting note on the NOW() (or CURRENT_TIMESTAMP), they are equivalent to transaction_timestamp(), which means - start time of the current transaction.

So, if you'd insert multiple items in a single transaction, all of them would end up with the same value in the "created" column.

Re: Bug story: Sorting by timestamp

#9

Post by loloquwowndueo » Sat, Dec 23, 2023, 9:51 PM UTC

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.

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.

Re: Bug story: Sorting by timestamp

#10

Post by dotancohen » Sat, Dec 23, 2023, 10:02 PM UTC

loloquwowndueo wrote: post #9

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.

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.

I'm not in the US.