0

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?

Stu
  • 30,392
  • 6
  • 14
  • 33
Jakub Znamenáček
  • 766
  • 1
  • 4
  • 18

2 Answers2

0

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

A.Steer
  • 335
  • 3
  • 14
  • `DATEDIFF` is rather different between different database engines. For example, MySQL DATEDIFF only takes two dates/timestamps. – SEoF Jan 27 '22 at 15:39
  • 1
    cheers for pointing that our, Ive changed my answer to specifically referance T-SQL. When the OP hasent been clear in their question on what engine they are using, as MySQL dosent have Interval datatypes I presumed that the question was referanceing an engine that does (T-SQL or PostgreSQL ect) – A.Steer Jan 27 '22 at 15:51
0

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 >
Chetan Ghodasara
  • 93
  • 7