Interesting note on the NOW() (or CURRENT_TIMESTAMP), they are equivalent to transaction_timestamp(), which means - start time of the current transaction.<p>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.
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.<p>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.
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.
Sidenote on showing (or not showing) timestamps.<p>Consider a page with 10 rows, and in each row you show the relative date.<p>2 issues with that:<p>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.<p>I hate relative dates.
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.
This title is instant PTSD flashback - at one place I worked there was a system that would order events by their timestamps and multiple downstream systems would rely on that.<p>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.<p>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 :)
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;<p>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