Search code examples
databasevertica

Vertica: ORDER BY ASC and DESC for date and time is giving same value

I'm using Vertica db and trying to sort the rows by DESC and ASC in which both gives the same output. The rows value are in date and time like shown below.

testdb=> SELECT queue_entry_timestamp, acquisition_timestamp, (acquisition_timestamp-queue_entry_timestamp) AS 'queue wait' FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001' AND pool_name = 'test01' ORDER BY 'queue_entry_timestamp' desc limit 5;
     queue_entry_timestamp     |     acquisition_timestamp     |   queue wait
-------------------------------+-------------------------------+-----------------
 2023-01-27 03:40:02.259231+00 | 2023-01-27 03:40:02.259239+00 | 00:00:00.000008
 2023-01-27 03:40:02.258851+00 | 2023-01-27 03:40:02.25888+00  | 00:00:00.000029
 2023-01-27 03:40:45.958846+00 | 2023-01-27 03:40:45.958872+00 | 00:00:00.000026
 2023-01-27 03:41:41.958178+00 | 2023-01-27 03:41:41.958185+00 | 00:00:00.000007
 2023-01-27 03:42:42.966845+00 | 2023-01-27 03:42:42.966851+00 | 00:00:00.000006
(5 rows)


testdb=> SELECT queue_entry_timestamp, acquisition_timestamp, (acquisition_timestamp-queue_entry_timestamp) AS 'queue wait' FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001' AND pool_name = 'test01' ORDER BY 'queue_entry_timestamp' asc limit 5;
     queue_entry_timestamp     |     acquisition_timestamp     |   queue wait
-------------------------------+-------------------------------+-----------------
 2023-01-27 03:40:02.259231+00 | 2023-01-27 03:40:02.259239+00 | 00:00:00.000008
 2023-01-27 03:40:02.258851+00 | 2023-01-27 03:40:02.25888+00  | 00:00:00.000029
 2023-01-27 03:40:45.958846+00 | 2023-01-27 03:40:45.958872+00 | 00:00:00.000026
 2023-01-27 03:41:41.958178+00 | 2023-01-27 03:41:41.958185+00 | 00:00:00.000007
 2023-01-27 03:42:42.966845+00 | 2023-01-27 03:42:42.966851+00 | 00:00:00.000006
(5 rows)

Could someone let me know how to sort this ?

Solution

  • The problem with your query is the order by syntax:

    ORDER BY 'queue_entry_timestamp'

    This actually says to order by a constant string literal 'queue_entry_timestamp'. This value will always be the same, for every record in your table. So your current logic really has no ordering at all. Use this version:

    ORDER BY queue_entry_timestamp