postgreSQL sorting with timestamps

postgresql postgresql-9.4
Garamaru picture Garamaru · Jul 19, 2016 · Viewed 13.9k times · Source

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. SQL Editor with result So has anyone an idea why the result is this strange?

Answer

klin picture klin · Jul 19, 2016

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

Related questions

How to perform update operations on columns of type JSONB in Postgres 9.4

Looking through the documentation for the Postgres 9.4 datatype JSONB, it is not immediately obvious to me how to do updates on JSONB columns. Documentation for JSONB types and functions: http://www.postgresql.org/docs/9.4/static/functions-json.html http://www.postgresql.…

Read More
Merging Concatenating JSON(B) columns in query

Using Postgres 9.4, I am looking for a way to merge two (or more) json or jsonb columns in a query. Consider the following table as an example: id | json1 | json2 ---------------------------------------- 1 | {'a':'b'} | {'c':'d'} 2 | {'a1':'b2'} | {'f':{…

Read More
How to delete replication slot in postgres 9.4

I have replication slot which I want to delete but when I do delete I got an error that I can't delete from view. Any ideas? postgres=# SELECT * FROM pg_replication_slots ; slot_name | plugin | slot_type | datoid | database | active | …

Read More