title: Sqlite3: Need to Cartesian On date
body: <p>I have a table which is a list of games that have been played in a sqlite3 database.  The field "datetime" is the a datetime of when game ended.  The field "duration" is the number of seconds the game lasted.  I want to know what percent of the past 24 hours had at least 5 games running simutaniously.  I figured out to tell how many games running at a given time are:</p>
  
  <pre><code>select count(*)
  from games
  where strftime('%s',datetime)+0 &gt;= 1257173442 and
        strftime('%s',datetime)-duration &lt;= 1257173442
  </code></pre>
  
  <p>If I had a table that was simply a list of every second (or every 30 seconds or something) I could do an intentional cartisian product like this:</p>
  
  <pre><code>select count(*)
  from (
    select count(*) as concurrent, d.second
    from games g, date d
    where strftime('%s',datetime)+0 &gt;= d.second and
          strftime('%s',datetime)-duration &lt;= d.second and
          d.second &gt;= strftime('%s','now') - 24*60*60 and
          d.second &lt;= strftime('%s','now')
    group by d.second) x
  where concurrent &gt;=5
  </code></pre>
  
  <p>Is there a way to <strong>create this date table on the fly</strong>? Or that I can get <strong>a similar effect to this without having to actually create a new table</strong> that is simply a list of all the seconds this week?</p>
  
  <p>Thanks</p>