问题:

I have the following SQL statement:

SELECT * FROM schema."table"
  WHERE "TimeStamp"::timestamp >= '2016-03-09 03:00:05'
  ORDER BY "TimeStamp"::date asc
  LIMIT 15

What do I expect it to do? Giving out 15 rows of the table, where the timestamp is the same and bigger than that date, in ascending order. But postgres sends the rows in the wrong order. The first item is on the last position. So has anyone an idea why the result is this strange?

回答1:

Use simply ORDER BY "TimeStamp" (without casting to date).

回答2:

By casting "TimeStamp" to date you throw away the time part of the timestamp, so all values within one day will be considered equal and are returned in random order. It is by accident that the first rows appear in the order you desire.

Don't cast to date in the ORDER BY clause if the time part is relevant for sorting.

Perhaps you are confused because Oracle's DATE type has a time part, which PostgreSQL's doesn't.