-2

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

Student_OKIE
  • 79
  • 8
  • 3
    ```TRUNC(date)``` – alexherm Feb 24 '23 at 19:51
  • 2
    If you truncate it then you will still see the time, based on what your session's NLS settings seem to be from what you see now; but it will be midnight, so '0:00 AM' I guess. Oracle dates *always* have a time. If you don't want to see it then either change your NLS settings which will affect all dates, or use `to_char()` to format them with just the date elements. (Also, epoch time is UTC, so you might want to convert to your time zone - before truncating *8-) – Alex Poole Feb 24 '23 at 19:56
  • 1
    TRUNC() only works if your client (Oracle Developer) has built-in logic to auto-hide the 00:00:00. Other clients would probably show you the 00:00:00. So for universal display, change it to a string via TO_CHAR() and a specific date map { e.g. TO_CHAR(completed,'MM/DD/YYYY') } – Paul W Feb 24 '23 at 20:01
  • How would I reword either the created or completion to change to TO_CHAR? – Student_OKIE Feb 24 '23 at 20:07
  • PS. If I should be using "TO_CHAR" isn't that going to convert the date to a string? I ask because another part of my query subtracts completed from creation to get an age at completion. That subtraction wont work if I am converting to a string right? – Student_OKIE Feb 24 '23 at 20:35
  • You should do any date arithmetic as dates, before any string conversion. You use TO_CHAR at the end to convert it to a string exclusively for desired display in your client. And I should note that usually folks don't do that. They are okay with whatever format their client wants to show, or they configure a default date format in the client and let their client do the conversion by setting NLS_DATE_FORMAT and implicitly adding a TO_CHAR() around the fetched column. That's what my favorite client (DBArtisan) does. You really don't want to be having to mess with this for every query with a date – Paul W Feb 24 '23 at 20:40
  • I think I need to redo this post anew cause I should have included the arithmetic part cause that is what's causing problems. I got the TRUNC and TO_CHAR figured out but together they can't do simple subtraction cause 1-15 is 14 not 13. – Student_OKIE Feb 24 '23 at 21:03

1 Answers1

0

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;
MT0
  • 143,790
  • 11
  • 59
  • 117