SQLITE3: Need Cartesi Dating

select count(*)
from games
where strftime('%s',datetime)+0 >= 1257173442 and
 strftime('%s',datetime)-duration <= 1257173442 
If my table is just a list every second (or every 30 seconds or something), I can make an intentional Cartier product as follows:
 
 
select count(*)
from (
 select count(*) as concurrent, d.second
 from games g, date d
 where strftime('%s ',datetime)+0 >= d.second and
 strftime('%s',datetime)-duration <= d.second and
 d.second >= strftime('%s',' now')-24*60*60 and
 d.second <= strftime('%s','now')
 group by d.second) x
where concurrent >=5 
Is there a way to dynamically create this date table? Or can I get a similar effect without having to actually create a new table, which is just a list of all seconds of the week? 
Thank you

select sum(concurrent_period) from (
 select min(end_table.datetime- begin_table.begin_time) as concurrent_period
 from (
 select g1.datetime, g1.num_end, count(*) as concurrent
 from (
 select datetime, count(*) as num_end 
 from games group by datetime
) g1, games g2
 where g2.datetime >= g1.datetime and
 g2.datetime-g2.duration  g1.datetime >= strftime('%s','now')-24*60*60 and
 g1.datetime <= strftime('%s','now')+0
) end_table, (
 select g3.begin_time, g1.num_begin, count(*) as concurrent
 from (
 select datetime-duration as begin_time,
 count(*) as num_begin
 from games group by datetime-duration
) g3, games g4
 where g4.datetime >= g3.begin_ti me and
 g4.datetime-g4.duration  g3.begin_time >= strftime('%s','now')-24*60*60 and
 g3 .begin_time >= strftime('%s','now')+0
) begin_table
 where end_table.datetime> begin_table.begin_time
 and begin_table.concurrent <5
 and begin_table.concurrent+begin_table.num_begin >= 5
 and end_table.concurrent >= 5
 and end_table.concurrent-end_table.num_end <5
 group by begin_table.begin_time
) aah 

I have a table which is a list of games played in the sqlite3 database. The field "datetime" is the date and time when the game ends. The field "duration" is the game The number of seconds to last. I want to know what percentage of the last 24 hours have at least 5 games played at the same time. I want to know how many games are running at a given time:

select count(*)
from games
where strftime('%s',datetime)+0 >= 1257173442 and
 strftime('%s',datetime)-duration < = 1257173442

If my table is just a list every second (or every 30 seconds or something), I can make an intentional Cartier product as follows:

select count(*)
from (
 select count(*) as concurrent, d.second
 from games g, date d
 where strftime ('%s',datetime)+0 >= d.second and
 strftime('%s',datetime)-duration <= d.second and
 d.second >= strftime('% s','now')-24*60*60 and
 d.second <= strftime('%s','now')
 group by d.second) x
where concurrent >=5

Is there a way to dynamically create this date table? Or can I get a similar effect without having to actually create a new table, which is just a list of all seconds of the week?

Thank you

Good question!

This is a query that I think can provide you with what you need without using a separate form. Please note that this is untested (and therefore may contain errors), I Suppose datetime is an int column with seconds to avoid a lot of strftime.

select sum(concurrent_period) from (
 select min(end_table.datetime- begin_table.begin_time) as concurrent_period
 from (
 select g1.datetime, g1.num_end, count(*) as concurrent
 from (
 select datetime, count(*) as num_end 
 from games group by datetime
) g1, games g2
 where g2.datetime >= g1.datetime and
 g2.datetime-g2.duration  g1.datetime >= strftime('%s','now')-24*60*60 and
 g1.datetime <= strftime('%s','now')+0
) end_table, (
 select g3.begin_time, g1.num_begin, count(*) as concurrent
 from (
 select datetime-duration as begin_time,
 count(*) as num_begin
 from games group by datetime-duration
) g3, games g4
 where g4.datetime >= g3.begin_time and
 g4.datetime-g4.duration  g3.begin_time >= strftime('%s','now')-24*60*60 and
 g3. begin_time >= strftime('%s','now')+0
) begin_table
 where end_table.datetime> begin_table.begin_time
 and begin_table.concurrent <5
 and begin_table .concurrent+begin_table.num_begin >= 5
 and end_table.concurrent >= 5
 and end_table.concurrent-end_table.num_end <5
 group by begin_table.begin_time
) aah< /pre> 

The basic idea is to make two tables: one uses #concurrent games at the beginning of each game, and the other uses #parallel games at the end. Then connect these tables together, only at the "key points" ”, where the number of concurrent games exceeds 5. For each key start time, please take the key end time that occurs fastest, and hope to provide at least 5 games running all time slots at the same time.
 < p>I hope it is not too complicated and useful!