Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 10 total |
calculating time fields |
Tue, Sep 16 2008 11:09 AM | Permanent Link |
andrej | hi,
let say, each record have two fields of Time type. One for start time and one for end time. I would like to calculate hours and minutes inbetween. How do I form a sql statement? XP, D6, dbisam 4.12 |
Tue, Sep 16 2008 11:40 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | andrej
In DBISAM you can simply subtract the two time fields to give milliseconds. After that it becomes a bit more tricky. ((Time2-Time1) / 60000) gives minutes ((Time2-Time1) / 60000) / 60 gives hours So SELECT TRUNC(((Time2-Time1) / 60000) / 60) AS HOURS , ((Time2-Time1) / 60000) - 60 * TRUNC(((Time2-Time1) / 60000) / 60) AS MINUTES FROM .... Assuming you only ever enter hours and minutes otherwise you'll have to handle the seconds and milliseconds yourself Roy Lambert [Team Elevate] |
Tue, Sep 16 2008 3:13 PM | Permanent Link |
andrej | tnx for your reply and it is, of course, working. BUT. What if Time1 is, let say, 23:30,
and Time2 is, let say 00:35? In other words, if both Time1 and Time2 are not from the same day? andrej Roy Lambert <roy.lambert@skynet.co.uk> wrote: andrej In DBISAM you can simply subtract the two time fields to give milliseconds. After that it becomes a bit more tricky. ((Time2-Time1) / 60000) gives minutes ((Time2-Time1) / 60000) / 60 gives hours So SELECT TRUNC(((Time2-Time1) / 60000) / 60) AS HOURS , ((Time2-Time1) / 60000) - 60 * TRUNC(((Time2-Time1) / 60000) / 60) AS MINUTES FROM .... Assuming you only ever enter hours and minutes otherwise you'll have to handle the seconds and milliseconds yourself Roy Lambert [Team Elevate] |
Tue, Sep 16 2008 3:50 PM | Permanent Link |
andrej | and another thing. I would like to SUM this hours and minutes according to a certain key.
Let say date. I would like to sum hours and minutes inside each day? please, advise andrej Roy Lambert <roy.lambert@skynet.co.uk> wrote: andrej In DBISAM you can simply subtract the two time fields to give milliseconds. After that it becomes a bit more tricky. ((Time2-Time1) / 60000) gives minutes ((Time2-Time1) / 60000) / 60 gives hours So SELECT TRUNC(((Time2-Time1) / 60000) / 60) AS HOURS , ((Time2-Time1) / 60000) - 60 * TRUNC(((Time2-Time1) / 60000) / 60) AS MINUTES FROM .... Assuming you only ever enter hours and minutes otherwise you'll have to handle the seconds and milliseconds yourself Roy Lambert [Team Elevate] |
Tue, Sep 16 2008 3:54 PM | Permanent Link |
"Robert" | "andrej" <andrej.bivic@abitrade.si> wrote in message news:BAF719E4-0D0F-4319-8B81-F05B7241B1D5@news.elevatesoft.com... > tnx for your reply and it is, of course, working. BUT. What if Time1 is, > let say, 23:30, > and Time2 is, let say 00:35? > In other words, if both Time1 and Time2 are not from the same day? > If you know that if time2 is less than time1 it is the next day (IOW, that there can not be more than 24 hrs between the two times) just add 86400000 to time2. SELECT TRUNC(((if (Time2 < Time1 then (Time2 + 86400000 - Time1) else (Time2-Time1))) / 60000) / 60) AS HOURS , etc. Robert |
Tue, Sep 16 2008 4:15 PM | Permanent Link |
andrej | Yes, yes! This is realy working!!
Can you, please, write down also for minutes? Please. andrej "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote: "andrej" <andrej.bivic@abitrade.si> wrote in message news:BAF719E4-0D0F-4319-8B81-F05B7241B1D5@news.elevatesoft.com... > tnx for your reply and it is, of course, working. BUT. What if Time1 is, > let say, 23:30, > and Time2 is, let say 00:35? > In other words, if both Time1 and Time2 are not from the same day? > If you know that if time2 is less than time1 it is the next day (IOW, that there can not be more than 24 hrs between the two times) just add 86400000 to time2. SELECT TRUNC(((if (Time2 < Time1 then (Time2 + 86400000 - Time1) else (Time2-Time1))) / 60000) / 60) AS HOURS , etc. Robert |
Wed, Sep 17 2008 2:21 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | andrej
>and another thing. I would like to SUM this hours and minutes according to a certain key. >Let say date. I would like to sum hours and minutes inside each day? >please, advise You'll have to use a script and select into a memory table to do this. Simply SUMing the HOURS and MINUTES will be ok for the hours but you'll probably end up with a minutes value in excess of 60 and you can't carry out calculations on the SUM. So something like SELECT DATEFIELD, SUM(TRUNC(((Time2-Time1) / 60000) / 60)) AS HOURS , SUM(((Time2-Time1) / 60000) - 60 * TRUNC(((Time2-Time1) / 60000) / 60)) AS MINUTES INTO "Memory\handm" FROM table GROUP BY DATEFIELD; SELECT DATEFIELD, HOURS + TRUNC(MINUTES / 60) AS HOURS, MINUTES - 60*TRUNC(MINUTES / 60) AS MINUTES FROM "Memory\Temp"; Totally untested. Also remember to drop the memory table when you've finished with it. Roy Lambert [Team Elevate] |
Wed, Sep 17 2008 3:16 AM | Permanent Link |
andrej | Robert, can you please write me a query for MINUTES like this one below?
If you know that if time2 is less than time1 it is the next day (IOW, that there can not be more than 24 hrs between the two times) just add 86400000 to time2. SELECT TRUNC(((if (Time2 < Time1 then (Time2 + 86400000 - Time1) else (Time2-Time1))) / 60000) / 60) AS HOURS , etc. Robert |
Wed, Sep 17 2008 10:03 AM | Permanent Link |
"Robert" | "andrej" <andrej.bivic@abitrade.si> wrote in message news:99014C92-E443-4DC6-BA19-F05126E15499@news.elevatesoft.com... > Yes, yes! This is realy working!! Calm down > Can you, please, write down also for minutes? Please. > Every place where you have "(Time2 - Time1)", replace with "if (Time2 < Time1 then (Time2 + 86400000 - Time1) else (Time2-Time1))" Robert |
Thu, Sep 18 2008 4:47 PM | Permanent Link |
andrej | Tnx
"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote: "andrej" <andrej.bivic@abitrade.si> wrote in message news:99014C92-E443-4DC6-BA19-F05126E15499@news.elevatesoft.com... > Yes, yes! This is realy working!! Calm down > Can you, please, write down also for minutes? Please. > Every place where you have "(Time2 - Time1)", replace with "if (Time2 < Time1 then (Time2 + 86400000 - Time1) else (Time2-Time1))" Robert |
This web page was last updated on Tuesday, April 23, 2024 at 08:10 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |