How do I remove the time portion from a epoch conversion in SQL

Question

I am trying to get ride of the time portion of a conversion from epoch time to human readable form. Here is what the data output looks like currently:

Ticket Number	Created	Completed
14111487	2/1/2023 1:36 AM	2/15/2023 6:00 AM
14116522	2/1/2023 11:18 PM	2/15/2023 6:00 AM
14116477	2/1/2023 11:23 PM	2/17/2023 9:25 PM
14116425	2/1/2023 10:47 PM	2/15/2023 5:05 PM

and here is what I want it to look like:

Ticket Number	Created	Completed
14111487	2/1/2023	2/15/2023
14116522	2/1/2023	2/15/2023
14116477	2/1/2023	2/17/2023
14116425	2/1/2023	2/15/2023

How the heck do I get rid of the time within my query, I don't need it. Here is the relevant parts of the query that I'm using:

SELECT
TICKET.TICK_NUM AS "Ticket Number",
TO_DATE('19700101', 'YYYYMMDD') + (DATECREATED / 86400) AS "Created", 
TO_DATE('19700101', 'YYYYMMDD') + (FIXEDTIME / 86400) AS "Completed"
FROM TICKET
WHERE 
TICKET.DATECREATED BETWEEN 1675209600 and 1677196800;

I am using Oracle Developer 21.4.3

Answer 1

If you want to set the time component to midnight then use FLOOR or TRUNC before adding to the date to round the number down to be a whole day:

SELECT TICKET.TICK_NUM AS "Ticket Number",
       DATE '1970-01-01' + FLOOR(DATECREATED / 86400) AS "Created", 
       DATE '1970-01-01' + FLOOR(FIXEDTIME / 86400) AS "Completed"
FROM   TICKET
WHERE  TICKET.DATECREATED BETWEEN 1675209600 and 1677196800;

SELECT TICKET.TICK_NUM AS "Ticket Number",
       DATE '1970-01-01' + TRUNC(DATECREATED / 86400) AS "Created", 
       DATE '1970-01-01' + TRUNC(FIXEDTIME / 86400) AS "Completed"
FROM   TICKET
WHERE  TICKET.DATECREATED BETWEEN 1675209600 and 1677196800;

Or TRUNCate the date after adding the epoch time:

SELECT TICKET.TICK_NUM AS "Ticket Number",
       TRUNC(DATE '1970-01-01' + DATECREATED / 86400) AS "Created", 
       TRUNC(DATE '1970-01-01' + FIXEDTIME / 86400) AS "Completed"
FROM   TICKET
WHERE  TICKET.DATECREATED BETWEEN 1675209600 and 1677196800;

Or, if you just want to format it as a date string to display it without the time component then use TO_CHAR:

SELECT TICKET.TICK_NUM AS "Ticket Number",
       TO_CHAR(DATE '1970-01-01' + DATECREATED / 86400, 'MM/DD/YYYY') AS "Created", 
       TO_CHAR(DATE '1970-01-01' + FIXEDTIME / 86400, 'MM/DD/YYYY') AS "Completed"
FROM   TICKET
WHERE  TICKET.DATECREATED BETWEEN 1675209600 and 1677196800;