TimesTen difference between two timestamps

Question

I would like to get difference between two columns (both TT_TIMESTAMP(26,6)) select timestamp1 - timestamp2 as diff from table; but getting this error: An interval data type must be specified for a datetime arithmetic result

Any ideas?

Answer 1

In T-SQL you would need to provide an internal type (year,month,day ect) that would identify the specific element that you are wanting to se the differance in.

A formula like the following should give you the differance in days for example in T-SQL:

DATEDIFF(day, timestamp1, timestamp2) AS DateDiff

W3 Schools has a good indicator of the various options you can use

Answer 2

TimestTen 18.1 Documentation Reference

For TimesTen Database,

The difference between the two TT_TIMESTAMP datatype columns results into an INTERVAL datatype. (Not TT_TIMESTAMP)

And to get the desired component of the INTERVAL datatype, we must use EXTRACT function.

Below is one example.

-- Data preparation
CREATE TABLE DEMO (A TT_TIMESTAMP, B TT_TIMESTAMP;

INSERT INTO DEMO VALUES (TT_TIMESTAMP '2022-01-01 01:01:01.000000', TT_TIMESTAMP '2022-01-05 01:01:01.000000');

-- Below will return an error (as expected)
SELECT B-A FROM DEMO;

2789: An interval data type must be specified for a datetime arithmetic result
The command failed.

So, for the actual difference, we need to calculate like below.

-- Extract data like below
SELECT EXTRACT(DAY FROM B-A) FROM DEMO;
< 4 >
SELECT EXTRACT(HOUR FROM B-A) FROM DEMO;
< 0 >
SELECT EXTRACT(MINUTE FROM B-A) FROM DEMO;
< 0 >
SELECT EXTRACT(SECOND FROM B-A) FROM DEMO;
< 0 >

-- Get SECONDS between two TT_TIMESTAMP columns
SELECT 
  (EXTRACT(DAY FROM B-A) * 24 * 3600
  + EXTRACT(HOUR FROM B-A) * 3600
  + EXTRACT(MINUTE FROM B-A) * 60
  + EXTRACT(SECOND FROM B-A)) 
FROM 
  demo;

< 345600 >