[HN Gopher] Bug story: Sorting by timestamp
___________________________________________________________________
Bug story: Sorting by timestamp
Author : adam-p
Score : 19 points
Date : 2023-12-23 16:53 UTC (6 hours ago)
(HTM) web link (adam-p.ca)
(TXT) w3m dump (adam-p.ca)
| senderista wrote:
| 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.
| dotancohen wrote:
| 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.
| WirelessGigabit wrote:
| 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.
| aljarry wrote:
| > 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.
| loloquwowndueo wrote:
| 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.
| dotancohen wrote:
| I'm not in the US.
| gwbas1c wrote:
| 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.
| gwbas1c wrote:
| > 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 for their entire lives
|
| WTF? Every single developer knows that their database schema
| should be in UTC and thus immune to DST issues.
| _kst_ wrote:
| 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.
| WirelessGigabit wrote:
| 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.
| Terr_ wrote:
| 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.
| aljarry wrote:
| 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.
___________________________________________________________________
(page generated 2023-12-23 23:00 UTC)