CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Mowgli
Mowgli

Reputation: 3512

How to subtract Sys with Timestamp from field and get hours and minutes

This is my current query

SELECT 
        TABLE1.OUT_NO,
        To_char(sysdate, 'MM-DD-YYYY HH24:MI:SS') as "Current_Time",
        To_char(TABLE2.time_stamp, 'MM-DD-YYYY HH24:MI:SS') as "Recorded_Time"
FROM TABLE1 TABLE1 LEFT OUTER JOIN TABLE2 TABLE2 ON TABLE1.OUT_NO = TABLE2.OUT_NO
WHERE TABLE1.OUT_NO > '12345'

I need to subtract Current_time - Recorded_Time and get result in Hours and Minutes.
how can I achieve this?

Upvotes: 0

Views: 261

Answers (2)

Ben
Ben

Reputation: 52853

Simply subtract one from the other

select sysdate - systimestamp from dual;

Ensure that they're still a DATE and a TIMESTAMP, do not convert them to characters first. This returns an INTERVAL datatype.

Apparently this is giving you an integer, which means your timestamp is being implicitly converted to a date (or is in fact already a date and not a timestamp).

If this is the case you have the number of days between the two dates; multiply by the number of minutes in a day in order to get this in minutes:

select ( sysdate - systimestamp ) * 60 * 24 from dual;

Upvotes: 1

user2065377
user2065377

Reputation: 458

use datediff() built-in function

    SELECT 
    TABLE1.OUT_NO,
    To_char(sysdate, 'MM-DD-YYYY HH24:MI:SS') as "Current_Time",
    To_char(TABLE2.time_stamp, 'MM-DD-YYYY HH24:MI:SS') as "Recorded_Time",
    datediff(mi,time_stamp,getdate()) as DiffInTime
    FROM TABLE1 TABLE1 LEFT OUTER JOIN TABLE2 TABLE2 ON TABLE1.OUT_NO = TABLE2.OUT_NO
    WHERE TABLE1.OUT_NO > '12345'

will show results in minutes. Dance from there.

Upvotes: 0

Related Questions