Example of Unexpected Sort Order
This example shows a result set of TIME data type column values that do not seem to be sorted in the order specified in the ORDER BY clause. The local time values inserted into the column are followed by the values returned by the query.
Note: The time zone in this example is Pacific Time, which has a +8 hour time zone offset.
CREATE TABLE table_1
(column_1 INTEGER,
column_2 TIME);
INSERT INTO table_1 VALUEs ( 0, TIME'00:00:00.000000');
INSERT INTO table_1 VALUEs ( 1, TIME'01:00:00.000000');
INSERT INTO table_1 VALUEs ( 2, TIME'02:00:00.000000');
INSERT INTO table_1 VALUEs ( 3, TIME'03:00:00.000000');
INSERT INTO table_1 VALUEs ( 4, TIME'04:00:00.000000');
INSERT INTO table_1 VALUEs ( 5, TIME'05:00:00.000000');
INSERT INTO table_1 VALUEs ( 6, TIME'06:00:00.000000');
INSERT INTO table_1 VALUEs ( 7, TIME'07:00:00.000000');
INSERT INTO table_1 VALUEs ( 8, TIME'08:00:00.000000');
INSERT INTO table_1 VALUEs ( 9, TIME'09:00:00.000000');
INSERT INTO table_1 VALUEs (10, TIME'10:00:00.000000');
INSERT INTO table_1 VALUEs (11, TIME'11:00:00.000000');
INSERT INTO table_1 VALUEs (12, TIME'12:00:00.000000');
INSERT INTO table_1 VALUEs (13, TIME'13:00:00.000000');
INSERT INTO table_1 VALUEs (14, TIME'14:00:00.000000');
INSERT INTO table_1 VALUEs (15, TIME'15:00:00.000000');
INSERT INTO table_1 VALUEs (16, TIME'16:00:00.000000');
INSERT INTO table_1 VALUEs (17, TIME'17:00:00.000000');
INSERT INTO table_1 VALUEs (18, TIME'18:00:00.000000');
INSERT INTO table_1 VALUEs (19, TIME'19:00:00.000000');
INSERT INTO table_1 VALUEs (20, TIME'20:00:00.000000');
INSERT INTO table_1 VALUEs (21, TIME'21:00:00.000000');
INSERT INTO table_1 VALUEs (22, TIME'22:00:00.000000');
INSERT INTO table_1 VALUEs (23, TIME'23:00:00.000000');
This SELECT statement is used to query the time values in the TIME data type column (column_2).
SELECT * FROM table_1
ORDER BY column_2;
column_1 column_2
-------- ---------------
16 16:00:00.000000
17 17:00:00.000000
18 18:00:00.000000
19 19:00:00.000000
20 20:00:00.000000
21 21:00:00.000000
22 22:00:00.000000
23 23:00:00.000000
0 00:00:00.000000
1 01:00:00.000000
2 02:00:00.000000
3 03:00:00.000000
4 04:00:00.000000
5 05:00:00.000000
6 06:00:00.000000
7 07:00:00.000000
8 08:00:00.000000
9 09:00:00.000000
10 10:00:00.000000
11 11:00:00.000000
12 12:00:00.000000
13 13:00:00.000000
14 14:00:00.000000
15 15:00:00.000000
Because the sort order is based on UTC time (which is not visible in the column), the time values do not agree with the resulting sort order. Rows 16 through 23 appear at the top of the list because they belong at the top of the list in terms of UTC time.
The UTC equivalents for the returned time values are:
Returned Values UTC Equivalents (local time + 8 hours)
column_1 column_2
-------- -------------
16 16:00:00.000000 00:00:00.000000
17 17:00:00.000000 01:00:00.000000
18 18:00:00.000000 02:00:00.000000
19 19:00:00.000000 03:00:00.000000
20 20:00:00.000000 04:00:00.000000
21 21:00:00.000000 05:00:00.000000
22 22:00:00.000000 06:00:00.000000
23 23:00:00.000000 07:00:00.000000
0 00:00:00.000000 08:00:00.000000
1 01:00:00.000000 09:00:00.000000
2 02:00:00.000000 10:00:00.000000
3 03:00:00.000000 11:00:00.000000
4 04:00:00.000000 12:00:00.000000
5 05:00:00.000000 13:00:00.000000
6 06:00:00.000000 14:00:00.000000
7 07:00:00.000000 15:00:00.000000
8 08:00:00.000000 16:00:00.000000
9 09:00:00.000000 17:00:00.000000
10 10:00:00.000000 18:00:00.000000
11 11:00:00.000000 19:00:00.000000
12 12:00:00.000000 20:00:00.000000
13 13:00:00.000000 21:00:00.000000
14 14:00:00.000000 22:00:00.000000
15 15:00:00.000000 23:00:00.000000