Double the current time

Question

I have a query in which I am calculating time. I want to make time double so that if the time is 01:40 then it should become 03:20. Here is my current query

SELECT TO_CHAR(TRUNC((ATN_INN-ACT_INN)*24),'09')||':'||  
TO_CHAR(TRUNC(((ATN_INN-ACT_INN)*24-TRUNC((ATN_INN-ACT_INN)*24))*60),'09') B
FROM SML.EMP_INFO A

How should I go about this?

i am calculating total time as `01:40` now i make it double as `03:20` — Adeel Aslam, Jan 24 '13 at 10:48
I understand; but if you're only going to do it for _one_ time then there's no point calculating anything. You might just as well use a CASE statement. If you're going to do it for more than just the one time then you need to answer my question in order for your question to be answerable. — Ben, Jan 24 '13 at 10:51
not one time i want to calculate in query so affect on all dates — Adeel Aslam, Jan 24 '13 at 10:54
Yes, as I said I understand, what time is double 11:59 PM (23:59)? or 12:01 PM (anything after midday)? — Ben, Jan 24 '13 at 10:54
this is not matter of AM or PM This Total time in Hour and minutes — Adeel Aslam, Jan 24 '13 at 10:55
What if the time difference is e.g. 36 hours and 32 minutes, what outcome are you expecting? — GTG, Jan 24 '13 at 10:58
Suppose total Time Is 1 Hour and 40 Minutes Right ? Now if want to make it double so i multiply it with 2 then it become 2 hour and 80 miuntes lilke `02:80` but this wrong it should '03:20'.i thnk nw u got — Adeel Aslam, Jan 24 '13 at 10:59
No, it's not. 46 hours is 46 hours there is no possible way to have 40 minutes out of this. Please stop re-stating your question. No one is going to be able to help you effectively until you answer either my of @GTG's questions... Is 46 hours measured in days and hours or just in hours, if it's measured in just hours is it 46 hours or 22 hours? — Ben, Jan 24 '13 at 11:16
I think that the issue that Ben is trying to highlight to you is that there is a logical difference between a time (which is when an event occurred) and a time-*span*. You're wanting to work with time-spans, not times. I'm not sure if Oracle has a suitable data type for representing time-spans, but you shouldn't expect the time type to have operations only suitable for a time-span. — Damien_The_Unbeliever, Jan 24 '13 at 11:21
@Damien_The_Unbeliever: there is a data type representing time stamps: it's called `interval` — , Jan 24 '13 at 11:30

APC · Accepted Answer · 2013-01-24T14:49:35.363

If you really what to work only with the time element, the easiest way to so is to employ the 'SSSSS' date mask, which returns the number of seconds after midnight. So this query will return double the number of seconds between the two columns:

select ( to_number(to_char(atn_inn, 'sssss')) 
          - to_number(to_char(acn_inn, 'sssss')) ) * 2 as double_diff_in secs
from sml.emp_info
/

Converting seconds into hours and minutes is left as an exercise for the reader.

Note that this query only makes sense if ATN_INN is later than ACT_INN but still on the same day. This is the clarification @Ben was trying to make (with no success). If this is not the case a different solution is required, something like ....

select ( ( extract ( hour from diff ) * 60) 
            + extract ( minute from diff  ) ) *2  as double_diff_in mins
from ( select to_dsinterval ( atn_inn - act_inn ) as diff
        from sml.emp_info )
/

This returns the doubled different in minutes. Again, rendering the output into a display format is left to the reader.

I always love your little asides! I have no idea if you're right but it's a good compromise (and extensible) solution. — Ben, Jan 24 '13 at 21:31

score 0 · Answer 2 · answered Jan 24 '13 at 11:17

It seems to me you are using the method described here (Subtraction between dates): http://www.akadia.com/services/ora_date_time.html

If you want to double the lenght of the period, you really only need to multiply the base time difference with 2, like this:

SELECT TO_CHAR(TRUNC((2*(ATN_INN-ACT_INN))*24),'09')
    || ':' ||  
    TO_CHAR(TRUNC(((2*(ATN_INN-ACT_INN))*24
        -TRUNC((2*(ATN_INN-ACT_INN))*24))*60),'09') B
FROM SML.EMP_INFO A

This will give you the hour part and the minutes part of your results, you will probably want to have the days part too.

Double the current time

2 Answers2