Hi Guys,
I have two date time fields, Start & Finish.
I want to subtract Start from Finish and put the result into a TimeStamp field called Duration.
I have tried all sorts of things but no success as yet.
Something like..
Update table
set Duration = Finish - Start
where .....

Help.

Regards & Tia,
Ian

Ian


Ahhhh INTERVAL

>I have two date time fields, Start & Finish.
>I want to subtract Start from Finish and put the result into a TimeStamp field called Duration.
>I have tried all sorts of things but no success as yet.
>Something like..
>Update table
>set Duration = Finish - Start
>where .....

What you get as raw data from Finish - Start is a number of milliseconds CASTing as a VARCHAR gets you near - try

select
jobno,
cast(lastmodified - created as varchar(20)),
created,
lastmodified
from jobtickets

but that's it.

OK pontification mode on

What you are doing is pretty much wrong - subtracting two timestamps gives you the interval between the two points you can not then pretend that that interval is another point in the data / time plenum. What you end up with in the raw form is milliseconds, that can be converted to a number of days, hours, minutes, seconds

pontification mode off

I'm still trying but have little hope of success

Roy

Ian


Been doing a bit more research. You can do things like

CAST((Finish - Start) YEAR AS INTEGER) AS xYear,
CAST((Finish - Start) MONTH  AS INTEGER) - CAST((Finish - Start) YEAR AS INTEGER) *12 AS xMonth,

Days is trickier since a month length is more variable than a year's According to Googe sql timestamps start c1970 so you can't create a timetamp of '0000-00-00 00:00:00.000' (I know I tried).

What do you actually want to show the duration in? Is it as your title suggests hours, minutes, seconds or would hours (and decihours) be sufficient? Then you could do something like

CAST(CAST((Finish - Start) MINUTE AS INTEGER) / 60 AS DECIMAL(19,2))


Roy Lambert

Hi Roy,
What I need from the date difference is DDD:HH:MM:SS
Days, DDD, can be anything from 000 to 999,
Hours, HH would be 00 to 23,
etc.

All in a VarChar(12).

Regards,
Ian

Ian

OK

Its a bit complicated

REPLACE(' ',':',
CAST(
SUBSTR(
CAST(Finish - Start AS VARCHAR(20))
FROM
1
FOR
POS('.', CAST(Finish - Start AS VARCHAR(20))   )-1
) AS VARCHAR(12)))


Now to explain

The initial calculation Fisnish - Start will result in a millisecond value (I think)

The first CAST to VARCHAR causes it to be formatted into days hours:minutes:seconds.milliseconds - I don't think its in the OLH but its somewhere in this newsgroup, and I guess its what Tim does to display the calculation value in EDBManager - I use 20 to  make sure I capture everything

The SUBSTR is to get rid of the milliseconds

The second CAST is because you can't assign the result of the calculation to a variable

The final CAST is to get it into a VARCHAR(12)

And last but not least REPLACE the space separator with another colin - eh voila

Roy Lambert

Ian,

REPLACE(' ' WITH ':' , CAST(CAST(End - Start AS INTERVAL DAY TO SECOND) AS VARCHAR(12)))

--
Fernando Dias
[Team Elevate]

Ian,

It's "Finish" not "End".
Sorry.

REPLACE(' ' WITH ':' , CAST(CAST(Finish - Start AS INTERVAL DAY TO SECOND) AS VARCHAR(12)))

--
Fernando Dias
[Team Elevate]

Ian,

It's "Finish" not "End".
Sorry.

REPLACE(' ' WITH ':' , CAST(CAST(Finish - Start AS INTERVAL DAY TO SECOND) AS VARCHAR(12)))

--
Fernando Dias
[Team Elevate]

Roy,

Wrong place, it was for Ian, sorry.
It's been a troubled morning

--
Fernando Dias
[Team Elevate]

Fernando


Don't worry I have lots of those - even though I'm retired.

Unfortunately, your solution won't necessarily work

Take the unCASTed result

1 1:01:23.000

CASTing to VARCHAR(12) results in

1 1:01:23.00

ie you have 2 places of milliseconds which is why I initially CAST to VARCHAR(20)

999:23:59:37.000

is 16 characters I think allowing for 9999999 days (slightly less than 30k years) should accommodate everything. Of course if a phone is in there being repaited for 30k years I think they should forget about the hours etc totally.

Roy Lambert