PostgreSQL does not order timestamp column correctly

Question

I have a table in a PostgreSQL database with a column of TIMESTAMP WITHOUT TIME ZONE type. I need to order the records by this column and apparently PostgreSQL has some trouble doing it as both

...ORDER BY time_column

and

...ORDER BY time_column DESC

give me the same order of elements for my 3-element sample of records having the same time_column value, except the amount of milliseconds in it.

It seems that while sorting, it does not consider milliseconds in the value.

I am sure the milliseconds are in fact stored in the database because when I fetch the records, I can see them in my DateTime field.

When I first load all the records and then order them by the time_column in memory, the result is correct.

Am I missing some option to make the ordering behave correctly?

EDIT: I was apparently missing a lot. The problem was not in PostgreSQL, but in NHibernate stripping the milliseconds off the DateTime property.

I must admit I'm finding it hard to believe that PostgreSQL can't sort a built-in type that's been there for more than a decade. — Richard Huxton
Please provide table definition and the insert commands for your 3-element sample that fails to sort. I concur with Richard, as I'm using timestamp without time zone in a database and have no problems with the sort. — Matt

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2012-03-01T22:40:50

It's a foolish notion that PostgreSQL wouldn't be able to sort timestamps correctly. Run a quick test and rest asured:

CREATE TEMP TABLE t (x timestamp without time zone);

INSERT INTO t VALUES
 ('2012-03-01 23:34:19.879707')
,('2012-03-01 23:34:19.01386')
,('2012-03-01 23:34:19.738593');

SELECT x FROM t ORDER by x DESC;
SELECT x FROM t ORDER by x;

q.e.d.

Then try to find out, what's really happening in your query. If you can't, post a testcase and you will be helped presto pronto.

PostgreSQL does not order timestamp column correctly

2 Answers