Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
luftgekühlt
Starting Member
1 Post |
 Posted - 2012-03-21 : 03:29:17
|
| I have a query that is working very well:select substr(to_char(timestamp, 'dd-mon-yy - hh24:mi:ss'), 1, 17), count(*)from MY_TABLEwhere 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')that groups 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. So far so good, it works fine.The problem is that records are not updated all the time, 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).MY QUESTION:I would like to change my sql statement in order to "fill in the blanks", i.e. for the periods where there are no updated timestamps in the table, I would like to fill in with seconds having 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? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-03-21 : 04:17:47
|
SQLTeam.com is on Microsoft SQL Server. For Oracle question, please post at dbforums.com or other
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
|
|
|
|
|
Adding missing timestamps