If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

How to "round" times

Started by hespora, 28 Dec 2017 05:20:30 AM

Previous topic - Next topic

hespora

I'm doing reporting on the audit package, and I want to cluster report executions into different timeslots during the day. i.e., I have this data coming striaght from the source:

Time Stamp
Dec 11, 2017 10:14:41 AM
Dec 11, 2017 2:50:36 PM
Dec 11, 2017 6:10:30 PM
Dec 12, 2017 8:04:16 AM
Dec 12, 2017 11:48:03 AM
Dec 12, 2017 3:18:19 PM


and from those, i want to derive these:

timeslot date
07:00:00 Dec 11, 2017
14:00:00 Dec 11, 2017
17:30:00 Dec 11, 2017
07:00:00 Dec 12, 2017
10:30:00 Dec 12, 2017
14:00:00 Dec 12, 2017


Now first, if I understand it correctly, cognos does not actually have separate date or time datatypes, only a date-timestamp. So if I need to work with times, I'll need a fixed date, and vice versa. Given that, getting the "date" column above would be easy using just _start_of_day from the business date/time functions?

But how would I go about a) transforming a datetime to a time with a fixed date, and then b) perform rounding? In the example data above, those timeslots are buckets of 3.5 hours starting at 7am, but any other interval and offset are conceivable.


hespora

Never mind, got it. Or, I got *some* approach; I have no clue if that is the most efficient. For what it's worth, if anyone ever needs that:

_add_seconds(
  current_date,
  (
    (
      (
        floor(
          (
            (
              _hour ([Time Stamp]) * 60 * 60
              +
              _minute ([Time Stamp]) * 60
              +
              _second ([Time Stamp])
            ) - (7 * 60 * 60)
          ) / (3.5 * 60 * 60)
        )
      ) * (3.5 * 60 * 60)
    ) + (7 * 60 * 60)
  )
)


First, calculate the seconds in the day since midnight, calculate the slots, where both "(3.5 * 60 * 60)" are the interval size, and both "(7 * 60 * 60)" are the offset, then add the seconds back up to a fixed date.

Lynn

Quote from: hespora on 28 Dec 2017 05:20:30 AM
Now first, if I understand it correctly, cognos does not actually have separate date or time datatypes, only a date-timestamp.

It is the database that houses your data source that will define the data type, not Cognos. Even for databases that do support date as well as date-timestamp data types the audit database and the framework manager package that provides this information only use the date/time data type.

I have my own modified Framework Model for the audit package to simplify analysis from audit information. There is a date field and an hour field in addition to the original datetime field so you can combine these as needed on any report without repeating the logic in every report. I also add in count metrics and other interval metrics that are often handy. Lastly, I join to a table in our data warehouse that identifies users so we can report on types of users such as by role or department in addition to having multiple ways to identify an individual (i.e., separate fields for first and last names plus "Last, First" and "First Last" fields).

hespora

Quote from: Lynn on 28 Dec 2017 06:23:01 AM
I have my own modified Framework Model for the audit package to simplify analysis from audit information. There is a date field and an hour field in addition to the original datetime field so you can combine these as needed on any report without repeating the logic in every report. I also add in count metrics and other interval metrics that are often handy.
Yeah, that makes perfect sense. Unfortunately, I'm a lowly report author and nothing more, so I do not get access to stuff like that. ;)

Invisi

I just looked at the audit package by accident and I have to say it [*^#$*] when it is about dates. No date dimension. I would indeed enhance the audit package to get some date dimension in it. Otherwise it's plumbing to get something useful for more advanced reporting on it.
Few can be done on Cognos | RTFM for those who ask basic questions...