0

I'm trying to calculate a time difference, for example: 

from 10:00 to 11:30

It must show 1:30 — that is the difference — but it's showing me 00000000 1:30:000000

How can I make this select statement show this answer? I tried many things but it doesn't work.

select TO_CHAR(TO_TIMESTAMP(ARRIVAL,'HH:MI:SS') - (TO_TIMESTAMP (START,'HH:MI:SS'))) as Du
from passenger;
user5520587
  • 23
  • 4
  • 1
    How is the difference from 10:30 to 11:30 equal to 1:30? Isn't it 1:00? –  Apr 25 '16 at 19:56
  • sorry i mean 10:00 to 11:30 – user5520587 Apr 25 '16 at 19:56
  • I disagree with marking this as an "exact" duplicate of the linked question, which has five answers - all for a DIFFRENT question, where the OP wanted to keep three decimals for seconds. The question asked in this (flagged) question does not request that, so the answers should be much simpler. Marking this as an "exact" duplicate of the older question is misleading. If you can find an EXACT duplicate link to that, otherwise reopen this question. Not sure if this is the right process to object to your flag... pls point me to the documentation if I missed it (I searched unsuccessfully). –  Apr 25 '16 at 20:07
  • Why it's show me a duplicate question but it's different, in my question i wanna show the result as hours and minutes like this for example :(1:30) but it's show me like this (+000000000 01:30:00.000000000) – user5520587 Apr 25 '16 at 20:09
  • 2
    @user5520587 - what that is giving you is an interval, and the duplicate is about how to format an interval. – Alex Poole Apr 25 '16 at 21:34
  • `SUBSTR( timestamp1 - timestamp2, 12, 5 )` or `REGEXP_SUBSTR( timestamp1 - timestamp2, '[12]?\d:\d\d' )` - either is a variation on the answers in the duplicate question. – MT0 Apr 25 '16 at 21:36
  • am using this but it's show me an error select (to_char(to_Date(END_TIME, 'HH24:MI'),'HH:MI'))- (to_Char(to_Date(START_TIME,'HH24:MI'),'HH:MI')) d from pass; – user5520587 Apr 25 '16 at 21:43

1 Answers1

0

The argument to the outermost application of to_char actually is of the type INTERVAL DAY TO SECOND and there is no associated format model. The trick is to cast that value back into a timestamp by adding a timestamp of 00:00:00:

select TO_CHAR( TO_TIMESTAMP('00:00:00','HH24:MI:SS') + (TO_TIMESTAMP(ARRIVAL,'HH:MI:SS') - TO_TIMESTAMP (START,'HH:MI:SS')), 'HH24:MI') as Du from passenger;

-- for testing on dual
select TO_CHAR( TO_TIMESTAMP('00:00:00','HH24:MI:SS') + (TO_TIMESTAMP('11:30:00','HH:MI:SS') - TO_TIMESTAMP ('10:00:00','HH:MI:SS')), 'HH24:MI') as Du from dual;
collapsar
  • 17,010
  • 4
  • 35
  • 61