CODEWITHSUNDEEP
sqloracle-database
achyrd
achyrd

Reputation: 411

Is it possible to order by truncated timestamp in SQL (oracle)?

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.

Upvotes: 0

Views: 263

Answers (2)

MT0
MT0

Reputation: 168470

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143033

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

Upvotes: 0

Related Questions