Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 14 total |
Time Difference in HH:MM:SS |
Tue, Sep 22 2020 10:27 PM | Permanent Link |
Ian Branch | 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 |
Wed, Sep 23 2020 4:01 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Sep 23 2020 7:49 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Sep 23 2020 4:09 PM | Permanent Link |
Ian Branch | 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 |
Thu, Sep 24 2020 3:07 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Sep 24 2020 6:19 AM | Permanent Link |
Fernando Dias Team Elevate | Ian,
REPLACE(' ' WITH ':' , CAST(CAST(End - Start AS INTERVAL DAY TO SECOND) AS VARCHAR(12))) -- Fernando Dias [Team Elevate] |
Thu, Sep 24 2020 6:21 AM | Permanent Link |
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] |
Thu, Sep 24 2020 6:22 AM | Permanent Link |
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] |
Thu, Sep 24 2020 6:23 AM | Permanent Link |
Fernando Dias Team Elevate | Roy,
Wrong place, it was for Ian, sorry. It's been a troubled morning -- Fernando Dias [Team Elevate] |
Thu, Sep 24 2020 8:55 AM | Permanent Link |
Roy Lambert NLH Associates 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |