Timestamp subtract and Interval data type

[Barilla]

Hi,

Is there a simple query that gives two timestamps difference in a format like 'yyyy mm dd hh:mm:ss'?

ANSI only defines year to month or day to second interval data types (and several variations) but nothing like 'year to second'.

I wonder if there is a way to get the answer in a pretty simple manner without using 'extract' or concatination.

Regards,
Barilla

 

[Dagon]

I don't understand this. What is the format of the timestamps that you are trying to use ?

 

[Barilla]

Dagon,

Thank you for the interest. Let's take two timesstamp literals for example. So the actual query looks like:

select timestamp'2002-12-31 12:00:00' - timestamp'1995-02-14 08:30:00' from dual;

And I wonder if I can have the result like ' 7 10 17 03:00:00' in a simple query.

Thanks,
Barilla

 

[Dagon]

I don't think you could do this easily. You could get the difference in days by subtracting the two dates:

select to_date('2002-12-31 12:00:00', 'YYYY-MM-DD HH24:MI:SS') - to_date('1995-02-14 08:30:00', 'YYYY-MM-DD HH24:MI:SS')
from dual

You would then have to convert the resulting number of days into the required format by dividing by 365.25 etc.

The best bet would probably be to write a small PL/SQL function to produce the output string you require and then use that in the SQL statement.

 

[Barilla]

Thank you Dagon. Now I know what I have to do.

-Barilla