All Forums UDA
marcmc 112 posts Joined 12/05
20 Sep 2007
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);

famalau 43 posts Joined 08/07
20 Sep 2007

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!

Regards,

Fabio

marcmc 112 posts Joined 12/05
21 Sep 2007

perfect Fabio.Thankyou very very much.

You must sign in to leave a comment.