4 Replies Latest reply: Sep 1, 2016 7:02 AM by Rüdiger Kladt RSS

    Sorting events into timeslots

    Rüdiger Kladt

      Hallo,

      I load some million events from a psql database. The events occures over the day at any time and the entity contains a datetime-field. I want to count the events occuring at different times and days of a week, aggregated over some years.

       

      Therefore i want to divide the 10080 minutes of a week in a numberd sequence of 10080/n slots, numbered from 1 to 10080/n. Example: MinutesOfTimeslot=15, timeslot nr.1 goes from 00:00:00 to 00:14:59 on monday an the last nr.672 from 23:45:00 to 23:59:59 on sunday.

       

      I think to do the calculation during the load-process like this:

       

      ODBC CONNECT TO PostgreSQL64;

      LOAD "eventTime",

      slot(enventTime,MinutesOfTimeslot) as slot_nr;

      SQL SELECT *

      FROM "events";

       

      I am not very experienced in SQL or QLIKVIEW and don't know if there are any existing functions fullfilling my wishes. Later it could be interesting to consider vacation and public holidays to compare similar days or weeks over the years.

       

      Any ideas?