CODEWITHSUNDEEP
sqloracle-databaseunixtimestamp
dingaro
dingaro

Reputation: 2342

How to convert unix timestamp and aggregate min and max date in Oracle SQL Developer?

I have table in Oracle SQL like below:

ID   | date          | place
-----------------------------
123  | 1610295784376 | OBJ_1
444  | 1748596758291 | OBJ_1
567  | 8391749204754 | OBJ_2
888  | 1747264526789 | OBJ_3

And I need to aggregate above date to achieve results as below, so I need to:

  1. convert unix timestamp in UTC from column "date" to normal date as below

  2. calculate min and max date for each values from column "place"

    min_date max_date distinct_place
    2022-01-05 2022-02-15 OBJ_1
    2022-02-10 2022-03-20 OBJ_2
    2021-10-15 2021-11-21 OBJ_3

Upvotes: 0

Views: 405

Answers (1)

MT0
MT0

Reputation: 168470

You can use:

SELECT TIMESTAMP '1970-01-01 00:00:00 UTC'
         + MIN(date_column) * INTERVAL '0.001' SECOND(3)
         AS min_date,
       TIMESTAMP '1970-01-01 00:00:00 UTC'
         + MAX(date_column) * INTERVAL '0.001' SECOND(3)
         AS max_date,
       place
FROM   table_name
GROUP BY place;

Note: the (3) after SECOND is optional and will just explicitly specify the precision of the fractional seconds.

or:

SELECT TIMESTAMP '1970-01-01 00:00:00 UTC'
         + NUMTODSINTERVAL( MIN(date_column) / 1000, 'SECOND')
         AS min_date,
       TIMESTAMP '1970-01-01 00:00:00 UTC'
         + NUMTODSINTERVAL( MAX(date_column) / 1000, 'SECOND')
         AS max_date,
       place
FROM   table_name
GROUP BY place;

Which, for the sample data:

CREATE TABLE table_name (ID, date_column, place) AS
SELECT 123, 1610295784376, 'OBJ_1' FROM DUAL UNION ALL
SELECT 444, 1748596758291, 'OBJ_1' FROM DUAL UNION ALL
SELECT 567, 1391749204754, 'OBJ_2' FROM DUAL UNION ALL -- Fixed leading digit
SELECT 888, 1747264526789, 'OBJ_3' FROM DUAL;

Both output:

MIN_DATE MAX_DATE PLACE
2021-01-10 16:23:04.376000000 UTC 2025-05-30 09:19:18.291000000 UTC OBJ_1
2014-02-07 05:00:04.754000000 UTC 2014-02-07 05:00:04.754000000 UTC OBJ_2
2025-05-14 23:15:26.789000000 UTC 2025-05-14 23:15:26.789000000 UTC OBJ_3

db<>fiddle here

Upvotes: 1

Related Questions