Actuate Programming Journal

Report design from a guy who thinks he knows what he’s doing; you decide . . . .


with 3 comments

Somewhere along the way I created a report to compare the elapsed time from OPEN to CLOSED to a target number of hours. And it works (or no one complained).

I must not have done enough research, because I learned today that in DB2 TODAY minus YESTERDAY doesn’t equal 1 day, 24 hours, 1440 minutes, or even 86400 seconds.

It equals 1000000.000000


The value returned when subtracting one date from the next looks like a number, feels like a number, acts like a number, but isn’t. Not really. Its a coded string. And the format is: YYYYMMDDHHMMSS.ZZZZZZ. It took several minutes of banging my head to come to grips with this silliness followed by more than a bit of screwing around to realize that the answer to my problem was to convert the static target values I comparing my results with to this format:

4 hours = 40000
48 hours = 2000000 (not 480000)
47 hours = 1230000

And that’s great for me for this report since my targets are static and I just hardcode them in to the SQL in the report. But what happens when I need to display the returned ‘numeric’ value as a pleasant (pleasant = appealing to customer) looking duration?

From 3/30/2006 8:59:23 AM to 4/6/2006 3:26:32 PM returns 7062709.000000 (not pleasant)

7 days, 6 hours, 27 minutes, and 9 seconds (pleasant)

You and I can do that our heads, but in a report we’d have to distill that logic into a complex parsing code in order to get that pleasant result. Sure, that just a little more code maybe a handy function, but come on, why isn’t pleasant the default?

Further, what if the customer wanted this in just hours or minutes? Not only would I have to parse the returned ‘numeric’ value as if it were a string, but I would have to come up with all the multiplication too. Don’t even ask me to think about how to deal with the fact that there aren’t a consistent number of days in a month.

I have to assume that sharper folks than I are building enterprise class databases and that they decided that this was best for us all. Anyone know why?

Consider yourselves warned.

Tags: , , ,


Written by Douglas

20061025 at 12:21 pm

Posted in SQL

3 Responses

Subscribe to comments with RSS.

  1. Hmmm what code did you use to obtain this? I don’t profess to know db2, but in SQL it would be

    datediff(dd, closed, open)



    20061031 at 06:37 am

  2. @ eyespi20

    Sadly, dateDiff( ) is not a function that DB2 v8 recognizes (afaik).

    Of course Actuate does and I could have modified the report to use that functionality. The two things that hindered me doing so were that the report was already built the one way and I am generally lazy about such overhauls and I sincerely believe in making the SQL do as much work as possible for my report.

    I think making the SQL deal with the data collection and even ‘creation’ in some cases is a superb way to encapsulate reporting logic. I hate finding reports with some of the requirements in the SQL, other requirements in the Fetch( ) (or OnRead() if the author prefers), and then more out in various Finish()es.


    20061031 at 07:53 am

  3. Found this post today. Thought I was in calculation hell too. Turns out, its not that bad.

    One can use the DAY, HOUR, MINUTE, SECOND functions on the result of YYYYMMDDHHMMSS.ZZZZZZ , with that, multiply appropriately to get your summed total in seconds, minutes , etc.




    20110308 at 08:51 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: