RE: TIMESTAMP

[Zabach, Elke]

Andre Reitz  wrote: 

"Zabach, Elke" schrieb:

 Andre Reitz  wrote:is there an easy way to get the difference of two timestamp fields in seconds,
independent of the timestamp-Format of the database?
If you can assure that those two timestamp fields do not differ more than 1 day:select (hours(mydiff)*3600 + minutes(mydiff)*60 + seconds(mydiff)   from (select timediff (one_timestamp, the_other_timestamp) mydiff from tab)that is not easy, but possible.If you cannot assure the small difference between those values, there is no easyway to do it.ElkeSAP Labs Berlin

Thank you very much, but this doesnt resolve the problem.

what about
SELECT NUM(Date1)-NUM(Date2) ?
Will this query succeed when an other TIMEStamp-Format ist set in the INSTANCE? 

    This will work with every date-and-time-format. No matter what date-and-time-format is specified,

the STORAGE of the data is the same, only the input/output is changed accordingly.

 

BUT !!!!!

The query will work, but will never result in the expected result.

Let give you an example with the last second (microseconds=0) of the last year and the first second of this year,

internal date and time format:

 

CREATE TABLE T (A TIMESTAMP, B TIMESTAMP) 

 

INSERT T VALUES ('20020101000001000000', '20011231235959000000')

 

SELECT A, B, NUM(A)-NUM(B) INTO :A, :B, :C FROM T

A                 :  20020101000001000000

B                 :  20011231235959000000
EXPRESSION1       :   8.8697640420000000000000000000000000000E+15

 

I am nearly sure you expected a result of 2 seconds, not the result given above.

But if you see those numbers (and forget that they were build using date-values),

the result is correct.

The function NUM will NOT give the number of seconds since 1.1.whatever year (01 or 1970 or so).

Therefore: NUM is of no real use for this problem and (as I wrote) I do not know an easy solution.

 

Elke

SAP Labs