31–40 of 49 posts
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.
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.
If there's an increasing ID, just sort by ID.
One day, I fixed an issue in the message producer that was causing the routine to take unreasonable amount of time and resources so that latency went from 1s to ~50ms.
Three hours later P1 is raised and the entire architecture had to be refactored. I still have the screenshot of the latency graph saved somewhere :)
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.
Where I live, South Africa, we have a single timezone which never has DST.
It is very easy for a developer here to be oblivious of time zones/DST as in practice it will never bite them in the local only market.
Well, I lie, it’s only almost never, a common smell of incorrect time handling is when the deployment instructions have a note to ensure the server is set to the correct time zone.
Tangental pet peeve of mine, in the early 2000s I received an abuse report with quoted logs with the timestamps being qualified as merely EST (or some other US time zone), which I found super annoying as I had to look up what the offset for such time zone was and then manually apply conversions before I could do any investigating of my own.
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.
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.
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.
As time went on, the jobs started taking longer and longer and eventually took longer than the whole local morning, which resulted in them being awake AND being able to complain about it. The other issue was DST... users who were used to waking up and getting their data right away... would either be delayed an hour, or be done an hour early. Sometimes, the jobs would also run 2x or not run at all.
It was an utter mess of unintended consequences.
Earlier quoted context omitted.
> 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.h…
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.
https://en.m.wikipedia.org/wiki/Daylight_saving_time_by_coun...
Earlier quoted context omitted.
It does not. I've got a specification that all inputs and outputs are in local time. Sometimes the timezone isn't known yet. Or at all.
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
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. How often and when you do this checking is ... not obvious.