Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Converting time difference to a given format in Oracle

How do I convert EVENT_DATE_B - EVENT_DATE_A which is a number of days to string with HH:MM format?

like image 332
lexeme Avatar asked Dec 25 '12 12:12

lexeme


People also ask

How would you format a date to mm/dd/yyyy in Oracle?

Use TO_CHAR to display it in any format you like. For example: SELECT TO_CHAR ( TO_DATE (date_value, 'yyyy-mm-dd') , 'mm/dd/yyyy' ) FROM table_x; Things are much easier if you store dates in DATE columns.

How can I find the difference between two timestamps in Oracle?

To calculate the difference between the timestamps in Oracle, simply subtract the start timestamp from the end timestamp (here: arrival - departure ). The resulting column will be in INTERVAL DAY TO SECOND . The first number you see is the number of whole days that passed from departure to arrival .


1 Answers

Another approach (one query can be on different days):

with tt as (
   select numToDsinterval((EVENT_DATE_B - EVENT_DATE_A ), 'DAY') dsint 
     from t)
select (extract(day from dsint)*24)+extract(hour from dsint) || 
       ':' ||extract(minute from dsint)
from tt

Here is a sqlfiddle demo

like image 76
A.B.Cade Avatar answered Nov 09 '22 21:11

A.B.Cade