Days Between

system · May 3, 2004, 1:06pm

Yes…I searched!

I want to put the “days between” in my universe rather than in my report. I am using DB2. I can use the “days” command and calculate that as a number. That’s good…but not good enough!

I need to calculate the days between two date/timestamps. My result should account for the hours, minutes, and seconds between the operations. Any suggestions on how to get this to the accuracy level I need?

Thanks!

Eileen King (BOB member since 2002-07-10)

system · May 3, 2004, 1:13pm

DB2 SQL References:

Subtracting Timestamps
The result of subtracting one timestamp (TS2) from another (TS1) is a timestamp duration that specifies the number of years, months, days, hours, minutes, seconds, and microseconds between the two timestamps. The data type of the result is DECIMAL(20,6).

If TS1 is greater than or equal to TS2, TS2 is subtracted from TS1. If TS1 is less than TS2, however, TS1 is subtracted from TS2 and the sign of the result is made negative. The following procedural description clarifies the steps involved in the operation result = TS1 - TS2:
If MICROSECOND(TS2) <= MICROSECOND(TS1)
     then MICROSECOND(RESULT) = MICROSECOND(TS1) -
        MICROSECOND(TS2).


If MICROSECOND(TS2) > MICROSECOND(TS1)
     then MICROSECOND(RESULT) = 1000000 +
        MICROSECOND(TS1) - MICROSECOND(TS2)
           and SECOND(TS2) is incremented by 1.
The seconds and minutes part of the timestamps are subtracted as specified in the rules for subtracting times.
If HOUR(TS2) <= HOUR(TS1)
     then HOUR(RESULT) = HOUR(TS1) - HOUR(TS2).


If HOUR(TS2) > HOUR(TS1)
     then HOUR(RESULT) = 24 + HOUR(TS1) - HOUR(TS2)
        and DAY(TS2) is incremented by 1.
The date part of the timestamps is subtracted as specified in the rules for subtracting dates.

Dave Rathbun (BOB member since 2002-06-06)

system · May 3, 2004, 1:32pm

You remain my hero! Thanks!

Eileen King (BOB member since 2002-07-10)

system · May 3, 2004, 2:57pm

However…

This is strange…I create an object called elapsed hours. The select statement is

HOUR(ORDERDIM_V2.EFFTIME-ORDERDIM_V2.LODTE)

It returns a result of 17. That’s right!

Since I really want to calculate days I want 17/24 to get the partial day. When I create another object

(HOUR(ORDERDIM_V2.EFFTIME-ORDERDIM_V2.LODTE))/24

It returns a result of 0.00. That’s wrong!

If I do the math in the report of “elapsed hours/24” it gives me .71 which is what I’m looking for.

This is v61a…any thoughts?

Eileen King (BOB member since 2002-07-10)

system · May 3, 2004, 3:23pm

What happens when you try:

(HOUR(ORDERDIM_V2.EFFTIME-ORDERDIM_V2.LODTE))/24.0

Andreas (BOB member since 2002-06-20)

system · May 3, 2004, 3:27pm

You’re my hero too!!!

Can anyone explain why I needed to add the decimal place???

Eileen King (BOB member since 2002-07-10)

system · May 3, 2004, 3:29pm

Sounds like an implicit Data Type conversion. Without the decimal point the DBMS considers the result set to be INTEGER (-1, 0, 1 , 2, 3,…), with the decimal point it converts it behind the scenes to data type REAL (-1.23, 0.0, 5.74, …).

Andreas (BOB member since 2002-06-20)

system · May 3, 2004, 3:37pm

That is the screwiest thing I think I’ve seen yet!!! Thanks for the help…

So for those following the suspense, the calculation I’ve arrived at to calculate the elapsed difference in to 100ths of a days (with timestamps) is

(days(ORDERDIM_V2.EFFTIME)-days(ORDERDIM_V2.LODTE))+((HOUR(ORDERDIM_V2.EFFTIME-ORDERDIM_V2.LODTE))*60+MINUTE(ORDERDIM_V2.EFFTIME-ORDERDIM_V2.LODTE))/1440.0

Eileen King (BOB member since 2002-07-10)

system · May 3, 2004, 3:59pm

Alright - I was avoiding this but could no longer contain myself.
I don’t understand all the contortions you are going thru. My little DB2 SQL reference say that timestamp durations (subtracting) return DEC(20,6) format – that format is ccyymmddhhmmss.micros. I would do the subtraction and then parse out whatever part of that you need.

Date durations return DEC(8,0) as yyyymmdd so subtracting a birthdate from current date might give you 000410530 or 41 years, 5 months, 30 days.
But then I may be wrong cause I always have to look this stuff up and figure it all out again.

scott copeland (BOB member since 2002-08-15)

system · May 3, 2004, 4:05pm

What I was trying to do was to get the elapsed time in days…the system I’m working on in Delivery Performance so they want to see all kinds of date/timestamps subtracted from one another to get the duration of an activity. I needed to get it in days…to the 1/100th of the day.

If there’s a better way, let me know…this was what my warped little mind came up with…

I needed to figure out how to turn five months, two days, seventeen hours, and thirty minutes into 154.76 days…

All positive criticism appreciated!

Eileen King (BOB member since 2002-07-10)

system · May 12, 2004, 1:47pm

Eileen,

You should download SQL cookbooks by Graeme Birchall and have a look at what he has to say on the DB2 ‘TIMESTAMPDIFF’ function.

There is an accuracy issue it seems, but it may be good enough after all

blom0344 (BOB member since 2002-09-04)

system · December 7, 2012, 7:06pm

Talking about Days Between:

If my variables are set like so:

DaysBetween(12/07/12; 12/01/12)

Then shouldn’t I get a negative -6?! I am getting 6

I don’t recall that the function returns the absolute value.

Joealyche (BOB member since 2012-02-29)

system · December 7, 2012, 7:14pm

Never mind, the developer had an Alerter set which impacted the result.

Thanks anway.

Joealyche (BOB member since 2012-02-29)