V9 & V12: Sorting of timestamp column in query result window

[DieterBu]

Example:

SELECT SYSTIMESTAMP FROM dual
UNION ALL
SELECT SYSTIMESTAMP + numtodsinterval(.555, 'SECOND') FROM dual
UNION ALL
SELECT SYSTIMESTAMP + numtodsinterval(.155, 'SECOND') FROM dual
UNION ALL
SELECT SYSTIMESTAMP + NUMTOYMINTERVAL(-2, 'MONTH') + numtodsinterval(1, 'DAY') FROM dual
UNION ALL
SELECT SYSTIMESTAMP + NUMTOYMINTERVAL(-1, 'MONTH') FROM dual;

Result:

SYSTIMESTAMP
07.02.17 14:09:47,280560000 +02:00
06.04.17 14:09:47,835560000 +02:00
06.04.17 14:09:47,435560000 +02:00
06.04.17 14:09:47,280560000 +02:00
06.03.17 14:09:47,280560000 +02:00

No matter how often I click on the sort button in the result header, it never is sorted chronologically ASC oder DESC or whatsoever.
It seems to perform an alphabetical sort.

Regards, Dieter

 

[Marco Kalter]

Timestamps are indeed sorted as strings on the client side. Therefore you either need to modify the "order by" clause for server side sorting, or use an NLS_TIMESTAMP_FORMAT and/or NLS_TIMESTAMP_TZ_FORMAT that starts with "YYYY.MM.DD" so that string sorting will lead to the correct result. You can set these formats in the Oracle Registry.

 

[DieterBu]

No big deal

I've set NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT to
YYYY-MM-DD HH24:MI:SS.FF and
YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
in my user environment, so the alphanumerical sort is identical to the chronological sort (except when comparing timestamps from various timezones) ...

 

[grant]

But NUMBER columns are sorted as numbers and DATE columns are sorted as dates etc. So it's expected that TIMESTAMP columns also would be sorted chronologically.

It's annoying when trying to visually analyze data in query results (sorting results in a grid by one column, then by another) and get a wrong sorting.
As mentioned before there are some roundabouts (changing NLS, manual to_char conversion and so on) but is not so convenient for such a great product.

Do you have any plans to support proper sorting of TIMESTAMP columns?

 

[Marco Kalter]

This is on the list of enhancement requests.

 

[grant]

Thank you.