calculatations on timestamps

UDA
N/A

calculatations on timestamps

How can I get the difference in End_dt & Start_dt timestamps in terms of hours and minutes using the following statement? If we cannot do it on insert, can it be done using a select on existing data?

insert into dev_data_t.marc (Extract_dt, Component, Start_dt, End_dt, Duration) values
('20070827', 'MarcTest', '2007-06-01 15:33:05.230000', '2007-06-01 16:34:05.230000', End_dt-Start_dt);

CREATE MULTISET TABLE dev_data_t.marc, NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Id INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
NO CYCLE),
Extract_dt VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Component VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Start_dt TIMESTAMP(6),
End_dt TIMESTAMP(6),
Duration VARCHAR(10)
)
PRIMARY INDEX (Id);
2 REPLIES

Re: calculatations on timestamps

I did some adjustments in your previous query:

insert into marc (Extract_dt, Component, Start_dt, End_dt, Duration) values
('20070827', 'MarcTest', '2007-06-01 15:33:05.230000', '2007-06-01 16:34:05.230000'
,cast((
((cast('2007-06-01 16:34:05.230000' as timestamp) - cast('2007-06-01 15:33:05.230000' as timestamp)) hour to minute)
) as char(10)))

Please, try it and see if it attends you.

Good Luck!
N/A

Re: calculatations on timestamps

perfect Fabio.

Thankyou very very much.