I have a use case where I want to order by time, but at a certain resolution. For example my schema saves timestamps out to 9 decimals (nanosecond precision), but I only want to order by minutes and use a different field to order within that minute. I tried this

select * from myTable order by (cast(myTimeStamp at time zone 'UTC' as timestamp) - to_timestamp('01-JAN-01'))/1000000000*60 desc, id desc;

To convert the timestamp into epoch and then divide to get minute precision. But this gives the wrong ordering. Also when I do a dump on the above command to understand the returned data type I see data type: typ=190 and I can't find that type in the oracle docs which adds to my confusion.

So I'm wondering what I'm missing? It should be possible to order by a truncated (to minute) timestamp, any help is appreciated.

CodePudding user response：

Convert the TIMESTAMP WITH TIME ZONE to the UTC time zone so that you can compare identical times and then TRUNCate it back to the start of the minute:

SELECT *
FROM   myTable
ORDER BY
       TRUNC(myTimeStamp at time zone 'UTC', 'MI') DESC,
       id DESC;

Which, for the sample data:

CREATE TABLE myTable(
  id          NUMBER,
  myTimestamp TIMESTAMP WITH TIME ZONE
);

INSERT INTO myTable(id, myTimestamp)
SELECT 1, TIMESTAMP '1970-01-01 00:00:00 UTC'              FROM DUAL UNION ALL
SELECT 2, TIMESTAMP '1970-01-01 00:00:00 America/New_York' FROM DUAL UNION ALL
SELECT 3, TIMESTAMP '1970-01-01 00:00:00 Asia/Hong_Kong'   FROM DUAL UNION ALL
SELECT 4, TIMESTAMP '1970-01-01 00:00:00 Europe/Paris'     FROM DUAL UNION ALL
SELECT 5, TIMESTAMP '1970-01-01 01:00:00 UTC'              FROM DUAL UNION ALL
SELECT 6, TIMESTAMP '1970-01-01 01:00:00 America/New_York' FROM DUAL UNION ALL
SELECT 7, TIMESTAMP '1970-01-01 01:00:00 Europe/Berlin'    FROM DUAL;

Outputs:

ID	MYTIMESTAMP
6	01-JAN-70 01.00.00.000000 AMERICA/NEW_YORK
2	01-JAN-70 00.00.00.000000 AMERICA/NEW_YORK
5	01-JAN-70 01.00.00.000000 UTC
7	01-JAN-70 01.00.00.000000 EUROPE/BERLIN
1	01-JAN-70 00.00.00.000000 UTC
4	01-JAN-70 00.00.00.000000 EUROPE/PARIS
3	01-JAN-70 00.00.00.000000 ASIA/HONG_KONG

If you want to see the values converted to UTC that are being used in the sorting process then just add it in the output:

SELECT t.*,
       TO_CHAR(TRUNC(myTimeStamp at time zone 'UTC', 'MI'), 'YYYY-MM-DD HH24:MI:SS')
         AS converted_ts
FROM   myTable t
ORDER BY
       TRUNC(myTimeStamp at time zone 'UTC', 'MI') DESC,
       id DESC;

Which outputs:

ID	MYTIMESTAMP	CONVERTED_TS
6	01-JAN-70 01.00.00.000000 AMERICA/NEW_YORK	1970-01-01 06:00:00
2	01-JAN-70 00.00.00.000000 AMERICA/NEW_YORK	1970-01-01 05:00:00
5	01-JAN-70 01.00.00.000000 UTC	1970-01-01 01:00:00
7	01-JAN-70 01.00.00.000000 EUROPE/BERLIN	1970-01-01 00:00:00
1	01-JAN-70 00.00.00.000000 UTC	1970-01-01 00:00:00
4	01-JAN-70 00.00.00.000000 EUROPE/PARIS	1969-12-31 23:00:00
3	01-JAN-70 00.00.00.000000 ASIA/HONG_KONG	1969-12-31 16:00:00

If you just use TRUNC without converting to a common time zone then it will order based on the date and time components without considering the relative difference in the time zones.

db<>fiddle here

CodePudding user response：

Why don't you then just truncate timestamp to minutes?

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:Mi:ss';

Session altered.

SQL> select systimestamp col_1,
  2         trunc(systimestamp, 'mi') col_2
  3  from dual;

COL_1                                    COL_2
---------------------------------------- -------------------
29.12.21 20:32:50,178000  01:00          29.12.2021 20:32:00

SQL>

Then you'd

order by trunc(timestamp_column, 'mi'),
         yet_another_column