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

• ID - ID of client
• date - date in Unix timestamp in UTC
• place - place of contact with client

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

CodePudding user response：

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