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

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 ?

CodePudding user response：

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