Timestamp sort order in Oracle
I've noticed some strange sort order when sorting by timestamp. First it sorts by time portion of the timestamp and then, for the records with the same time, date used for sort.
For me it looks completely wrong. Is there any way to tell Oracle to sort by date and then by time?
Thanks.
Field is defined as TIMESTAMP(6), without timezone. Here is the sorting result:
10-JAN-11 11.18.36.368000000 AM
09-JAN-11 11.18.33.700000000 AM
08-JAN-11 11.18.33.704000000 AM
07-JAN-11 11.18.33.700000000 AM
06-JAN-11 11.17.40.705000000 AM
05-JAN-11 11.17.42.611000000 AM
04-JAN-11 11.17.40.723000000 AM
02-JAN-11 04.45.09.553000000 PM
01-JAN-11 04.45.08.447000000 PM
31-DEC-10 04.45.08.447000000 PM
30-DEC-10 04.35.22.153000000 PM
29-DE开发者_运维技巧C-10 04.30.25.334000000 PM
12-JAN-11 12.00.00.000000000 AM
11-JAN-11 12.00.00.000000000 AM
10-JAN-11 12.00.00.000000000 AM
09-JAN-11 12.00.00.000000000 AM
08-JAN-11 12.00.00.000000000 AM
07-JAN-11 12.00.00.000000000 AM
Here is the query:
select period_end from table_name where entity_id=102167 order by period_end desc;
Dima,
Your timestamp format shows all components of the timestamp, except one: the century. Your last six rows must be from a previous century.
Regards,
Rob.
You probable have a timestamp with timezone
.
精彩评论