asked on

subtracting timestamp :seconds differences output as int

Hi I need to subtract 2 timestamps and use the result(in seconds) as an integer to be stored on a table

I have tried and then trying to convert this to a number
SELECT TO_CHAR((systimestamp - upd_tmst)*24*3600)
FROM t_process;

Outputs:

+000690855 07:34:42.355200000

TO_NUMBER will not work because of the milliseconds.

Any help appreciated.
John

CRagsdell

johnnyoracle,

Try the following:

SELECT DateDiff('s',[TimeStamp1],[TimeStamp2]) AS ElapsedTime FROM TableName;

DateDiff gives you the difference between to dates, the 's' designates seconds.

CRagsdell

The last line should read:

DateDiff gives you the difference between TWO dates, the 's' designates seconds.

CR

ASKER CERTIFIED SOLUTION

Helena Marková

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

suzieqt

I assume you are using Oracle?

Then if the times are always within the same day, convert each time into seconds:

to_number(to_char(date1,'HH24'))*3600 + to_number(date1,'MI'))*60 +_ to_char(date1,'SS')

- to_number(to_char(date2...

to get the difference in seconds

If the times will go over days, you will get a minus results and will have to add 24*60*60 to get the correct result

Suzanne

johnnyoracle

ASKER

Many thanks to both Henka and Suzanne, best option is to convert to char and then subtract.in response to CRagsDell ,thanks for the help,unfortunately DateDiff is not implemented in Oracle only in Sybase.

Dates are a tricky issue, and I am probably better off writing my own function DiffSecs() etc for constant timestamp issues.

Cheers,
John

suzieqt

I don't want to sound like a sore loser, but if you take away two times which are a minutes apart using this method you will get 100060 when presumably you would want 60?

Suzanne

shijee

Try this out, I have just tested with a couple of dates & it seems to be working

SELECT ((TRUNC(T2-T1)*86400)-TO_CHAR(T1,'SSSSS')) + TO_CHAR(T2,'SSSSS') FROM T;

assuming t1 and t2 are two dates in table 't'

shijee

hey, this must be more easy

SELECT (T2-T1)*3600*24 FROM T;