Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    5

    Unanswered: Adding missing timestamps in table

    I have an sql query that is working very well :

    select substr(to_char(timestamp, 'dd-mon-yy - hh24:mi:ss'), 1, 17), count(*)
    from MY_TABLE
    where timestamp between
    to_date('190312 14:21:00','ddmmyy HH24:mi:ss') and
    to_date('210312 21:59:59','ddmmyy HH24:mi:ss')
    group by substr(to_char(timestamp, 'dd-mon-yy - hh24:mi:ss'), 1, 17)
    order by
    to_date(substr(to_char(timestamp, 'dd-mon-yy - hh24:mi:ss'), 1, 17),'dd-mon-yy hh24:mi')

    grouping data (by timestamp) from a table. I draw a chart with the results of this statement showing how many records have been updated on each second of each hour. So far so good, it works fine.

    The problem is that records are not updated all the time (this is a feature not a fault), sometimes there are updates each second during several minutes, sometimes there is a pause of several hours. This is reflected in the chart by a non-proportional x-axis (time axis). This is not "user friendly".

    MY QUESTION:
    I need to improve my sql statement in order to "fill in the blanks", i.e. for the periods where there are no updated records in the table, I need to fill in with the seconds showing zero updated timestamps. That way my x-axis would become proportional showing all seconds in the hour, some of them with some amount of records updated (fetched from the table), others with zero records updated (filled in by the statement). Is it possible?
    Last edited by aircooled; 03-21-12 at 06:46.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    "Filling in blanks" is a highly DBMS dependent solution.

    What are you using? PostgreSQL? Oracle?

    You should also enclose your SQL statements in [code] tags to make them more readable.

    More details on that can be found in the online help (bottom left of each page):
    http://www.dbforums.com/misc.php?do=bbcode#code

  3. #3
    Join Date
    Mar 2012
    Posts
    5

    Adding missing timestamps in table

    Quote Originally Posted by shammat View Post
    "Filling in blanks" is a highly DBMS dependent solution.

    What are you using? PostgreSQL? Oracle?

    You should also enclose your SQL statements in [code] tags to make them more readable.

    More details on that can be found in the online help (bottom left of each page):
    http://www.dbforums.com/misc.php?do=bbcode#code
    I am using Oracle.
    Last edited by aircooled; 03-21-12 at 06:49. Reason: spell

  4. #4
    Join Date
    Mar 2012
    Posts
    5

    Adding missing timestamps in table

    My SQL statement:

    Code:
    select substr(to_char(timestamp, 'dd-mon-yy - hh24:mi:ss'), 1, 17), count(*)
    from MY_TABLE
    where timestamp between 
    to_date('190312 14:21:00','ddmmyy HH24:mi:ss') and 
    to_date('210312 21:59:59','ddmmyy HH24:mi:ss')
    group by substr(to_char(timestamp, 'dd-mon-yy - hh24:mi:ss'), 1, 17)
    order by 
    to_date(substr(to_char(timestamp, 'dd-mon-yy - hh24:mi:ss'), 1, 17),'dd-mon-yy hh24:mi')
    Last edited by aircooled; 03-21-12 at 06:48.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Is the column "timestamp" defined as the datatype "timestamp" or "date" (btw: it's not a good idea to use a reserved word for a column name).

    By the way: you don't need the substr() on your columns, simply use to_char(timestamp, 'dd-mon-yy - hh24:mi'). That has the same effect and makes your statement a bit more readable.

    The solution would be something like this:
    Code:
    with dt_range as (
       select to_date ('190312 14:21:00', 'ddmmyy HH24:mi:ss') as start_dt,
              to_date ('210312 21:59:59', 'ddmmyy HH24:mi:ss') as end_dt
       from dual       
    ),
    all_seconds as (
       select start_dt + interval '1' second * level as second_counter
       from dt_range
       connect by level < (select (end_dt - start_dt) * 24 * 60 * 60 from dt_range)
    )
    select to_char(second_counter, 'dd-mon-yy - hh24:mi'), 
           count(mt.timestamp)
    from all_seconds s
      left join my_table mt on mt.timestamp = s.second_counter 
    where s.second_counter between (select start_dt from dt_range) and (select end_dt from dt_range)
    group by to_char(second_counter, 'dd-mon-yy - hh24:mi')
    order by to_char(second_counter, 'dd-mon-yy - hh24:mi')
    The first CTE (common table expression) name dt_range is merely for convenience so that the start and end can be defined in a single place and does not need to be repeated all over.

    The second CTE generates as many rows as there are seconds between the start and end date defined in dt_range

    The final SELECT then does an outer join between "all seconds" and the actual table, counting the number of rows in the actual table for each timestamp.
    Last edited by shammat; 03-21-12 at 09:54.

  6. #6
    Join Date
    Mar 2012
    Posts
    5

    Adding missing timestamps in table

    Quote Originally Posted by shammat View Post
    Is the column "timestamp" defined as the datatype "timestamp" or "date" (btw: it's not a good idea to use a reserved word for a column name).

    By the way: you don't need the substr() on your columns, simply use to_char(timestamp, 'dd-mon-yy - hh24:mi'). That has the same effect and makes your statement a bit more readable.

    The solution would be something like this:
    Code:
    with dt_range as (
       select to_date ('190312 14:21:00', 'ddmmyy HH24:mi:ss') as start_dt,
              to_date ('210312 21:59:59', 'ddmmyy HH24:mi:ss') as end_dt
       from dual       
    ),
    all_seconds as (
       select start_dt + interval '1' second * level as second_counter
       from dt_range
       connect by level < (select (end_dt - start_dt) * 24 * 60 * 60 from dt_range)
    )
    select to_char(second_counter, 'dd-mon-yy - hh24:mi'), 
           count(mt.timestamp)
    from all_seconds s
      left join my_table mt on mt.timestamp = s.second_counter 
    where s.second_counter between (select start_dt from dt_range) and (select end_dt from dt_range)
    group by to_char(second_counter, 'dd-mon-yy - hh24:mi')
    order by to_char(second_counter, 'dd-mon-yy - hh24:mi')
    The first CTE (common table expression) name dt_range is merely for convenience so that the start and end can be defined in a single place and does not need to be repeated all over.

    The second CTE generates as many rows as there are seconds between the start and end date defined in dt_range

    The final SELECT then does an outer join between "all seconds" and the actual table, counting the number of rows in the actual table for each timestamp.
    Thanks a lot for your reply and for your correct remark "(btw: it's not a good idea to use a reserved word for a column name)." Actually the column is called something else I just wrote timestamp for clarity. In your query I added the correct column name.

    I executed your query but unfortunately it return 0 rows. It executes in about the same time as my original query 5-6 seconds from my PL/SQL Developer but returns no rows. No errors whatsoever. Just: 0 rows returned. The original query with the same time interval and on the same table returns some hundreds of rows.

    I am using PL/SQL Developer against Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    Last edited by aircooled; 03-22-12 at 05:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •