Oracle Display More Than 24 Hours

Oracle display more than 24 hours

You need to pull the time difference apart into its constituent day, hour, minute and second elements, combine the number of days * 24 with the number of hours, and stick it back together.

When subtracting dates you get the difference as the number of days, so you need to convert the fractional part into the other elements, which you can do with a combination of trunc and mod; using a CTE to make this slightly easier to follow and showing each valu eon its own as well as combined into a single string:

with y as (
  select id, end_time - start_time as runtime
  from mytable
)
select id,
  runtime,
  trunc(runtime) as days,
  24 * trunc(runtime) as day_hours,
  trunc(24 * mod(runtime, 1)) as hours,
  trunc(60 * mod(24 * (runtime), 1)) as minutes,
  trunc(60 * mod(24 * 60 * (runtime), 1)) as seconds,
  lpad(24 * trunc(runtime)
    + trunc(24 * mod(runtime, 1)), 2, '0')
    ||':'|| lpad(trunc(60 * mod(24 * (runtime), 1)), 2, '0')
    ||':'|| lpad(trunc(60 * mod(24 * 60 * (runtime), 1)), 2, '0')
    as runtime
from y;

        ID    RUNTIME       DAYS  DAY_HOURS      HOURS    MINUTES    SECONDS RUNTIME 
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
         1 .184918981          0          0          4         26         16 04:26:16 
         2 1.14465278          1         24          3         28         18 27:28:18 

You could also convert the dates to timestamps for the calculation, which gives you an interval type, and then use the extract function to get the elements instead; the principal is the same though:

with y as (
  select id,
    cast(end_time as timestamp) - cast (start_time as timestamp) as runtime
  from mytable
)
select id,
  runtime,
  extract (day from runtime) as days,
  24 * extract (day from runtime) as day_hours,
  extract (hour from runtime) as hours,
  extract (minute from runtime) as minutes,
  extract (second from runtime) as seconds,
  lpad(24 * extract (day from runtime) + extract (hour from runtime), 2, '0')
    ||':'|| lpad(extract (minute from runtime), 2, '0')
    ||':'|| lpad(extract (second from runtime), 2, '0')
    as runtime
from y;

        ID RUNTIME           DAYS  DAY_HOURS      HOURS    MINUTES    SECONDS RUNTIME 
---------- ----------- ---------- ---------- ---------- ---------- ---------- --------
         1 0 4:26:17.0          0          0          4         26         17 04:26:17 
         2 1 3:28:18.0          1         24          3         28         18 27:28:18 

Or a slight variation, getting the difference from the dates and then converting that to an interval:

with y as (
  select id,
    numtodsinterval(end_time - start_time, 'DAY') as runtime
  from mytable
)
...

SQL Fiddle demo.

Convert Oracle Datetime with more than 24 Hours

You can add the time value to the date value. But before you have to convert the time value to the amount of seconds. In Oracle a day have the value of 1. A half day (12 hours) is 0.5 and a second is 1/(24 * 60 * 60).

So here is an example:

WITH DS AS (
    SELECT 313025 AS V_TIME,
           20180307 AS V_DATE
      FROM DUAL
)
SELECT (TO_NUMBER(SUBSTR(V_TIME, 1, 2)) * 3600 
       + TO_NUMBER(SUBSTR(V_TIME, 3, 2)) * 60 
       + to_number(substr(v_time, 5, 2))) / (24 * 60 * 60)
       + TO_DATE(TO_CHAR(V_DATE), 'YYYYMMDD') AS YOUR_DATE
  FROM DS
;

Hope ist helps.

How to get 24 hours back time from current time in Oracle

If you subtract 1 from a date datatype (e.g. sysdate), it'll move you back one day. But, if you subtract it from a timestamp datatype value, Oracle will convert it to date and return a date (moreover, it'll be truncated).

See the following example:

SQL> select
  2    systimestamp val1,
  3    systimestamp - 1 val2,
  4    --
  5    systimestamp - interval '1' day val3
  6  from dual;

VAL1
-----------------------------------------------------
VAL2
--------
VAL3
-----------------------------------------------------
30.11.20 09:55:01,439352 +01:00
29.11.20
29.11.20 09:55:01,439352000 +01:00

SQL>

So, what you should do is to subtract an interval, i.e.

select *
from user
where created_date < systimestamp - interval '1' day;

Oracle query to find records took more than 24hrs to process

I think a lot of your problem most likely stems from the use of the subquery in your column list of your inner query. Maybe try using an analytic function instead. Something like this:

SELECT rcd.file_date,
       rcd.recived_on AS "Date received On",
       rcd.loaded_On "Date Processed On",
       to_char(rcd.recived_on, 'DY') AS "Day",
       round((rcd.loaded_On - rcd.recived_on) * 24, 2) AS "time required"
FROM   (SELECT tbl1.file_date,
               MIN(tbl2.recived_on) OVER (PARTITION BY tbl2.filename) AS recived_on,
               tbl1.loaded_On
        FROM   Table1 tbl1
        INNER JOIN Table1 tbl2 ON tbl1.id = tbl2.id
        WHERE    tbl1.FileState = 'Success'
        AND      trunc(loaded_On) BETWEEN '25-Feb-2020' AND '03-Mar-2020') rcd
WHERE  (rcd.loaded_On - rcd.recived_on) * 24 > 24;

Also, you were selecting some columns in the inner query and not using them, so I removed them.

get count of records in every hour in the last 24 hour

The following might be what you need. It seems to work when I run it against the all_objects view.

WITH date_range
  AS (SELECT TRUNC(sysdate - (rownum/24),'HH24') as the_hour
        FROM dual
      CONNECT BY ROWNUM <= 1000),
     the_data
  AS (SELECT TRUNC(created, 'HH24')  as cr_ddl, count(*) as num_obj
        FROM all_objects
      GROUP BY TRUNC(created, 'HH24'))   
SELECT TO_CHAR(dr.the_hour,'DD/MM/YYYY HH:MI AM'), NVL(num_obj,0)
FROM date_range dr LEFT OUTER JOIN the_data  ao
     ON ao.cr_ddl =  dr.the_hour
ORDER BY dr.the_hour DESC     

The 'date_range' generates a record for each hour over the past 24.

The 'the_data' does a count of the number of records in your target table based on the date truncated to the hour.

The main query then outer joins the two of them showing the date and the count from the sub-query.

I prefer both parts of the query in their own CTE because it makes the actual query very obvious and 'clean'.

In terms of your query you want this;

WITH date_range
  AS (SELECT TRUNC(sysdate - (rownum/24),'HH24') as the_hour
        FROM dual
      CONNECT BY ROWNUM <= 24),
     the_data
  AS (SELECT TRUNC(systemdate, 'HH24')  as log_date, count(*) as num_obj
        FROM transactionlog 
      GROUP BY TRUNC(systemdate, 'HH24'))   
SELECT TO_CHAR(dr.the_hour,'DD/MM/YYYY HH:MI AM'), NVL(trans_log.num_obj,0)
FROM date_range dr LEFT OUTER JOIN the_data  trans_log
     ON trans_log.log_date =  dr.the_hour
ORDER BY dr.the_hour DESC   

delete records older than 24 hours in oracle SQL

If you want older than 24 hours then do:

where event_date < sysdate - 1

If you want before yesterday, then do:

where event_date < trunc(sysdate) - 1

As for performance, that depends on how many rows are being deleted. If your table only has thousands of rows, then this is fine. If it has millions of rows, then you might want an index on event_date. Or, you might even want to take a different approach -- selecting the data into a temporary table, truncating the original table, and then re-inserting it.

How to key in and display date with hours and minutes into Oracle SQL Developer?

After some more poking around I found the right preference setting (screen shot below).

Under "Tools -> Preference" there is a Database:NLS node you can configure. The setting I changed was "Date Format", which I changed to 'YYYY-MM-DD HH24:MI:SS'.

I had to restart SQL Developer in order for these changes to take effect. After that, dates display as I want, and the input mask on the GUI also allowed me to edit time.

How to select records from last 24 hours using SQL?

SELECT * 
FROM table_name
WHERE table_name.the_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY)

---

Related Topics