SQL query to return one unix time to represent a day

I need to inform the user the days when an alert took place.

I have an alerts_table in my database which stores the unixtime and alerts that took place during that time.

select DISTINCT unixtime from alerttable; // returns all alerts

The problem is that i have thousands of alerts happening per day and i just want my query to return one UNIX time representing each day , so that i can tell the user if he had an alert on particular days.

Then i can convert the unix times to Days and show the user (example: You had an alert on 11/11/2023 , 12/11/2023 etc )

Thank you !!

Solution

I'm not sure if this is the best way, but it works:

SELECT 
  MIN(unixTime) AS unixtime, 
  TRUNC(TO_DATE( '1970-01-01', 'YYYY-MM-DD' ) 
        + NUMTODSINTERVAL(unixTime, 'SECOND' )) AS "day"
FROM yourtable
GROUP BY 
  TRUNC(TO_DATE( '1970-01-01', 'YYYY-MM-DD' ) 
        + NUMTODSINTERVAL(unixTime, 'SECOND' ))
ORDER BY 1;

So we use the function NUMTODSINTERVAL (see the documentation) in order to get the amount of seconds from the unixtime.

Since the unix time by its definition "starts" on 1970-01-01, we can add the seconds to this date and will get the date from the unixtime value.

Now we use TRUNC to get rid of the exact time and focus on the date only. So we can GROUP BY the date only and just select one unixtime per day (or the day itself if we prefer that).

We have to decide which unix time we select. I have chosen the first per day using MIN.

The ORDER BY clause sorts the result starting with the earliest day.

We can try out this solution on this sample fiddle