How select on interval of 15min for drawing charts with adding empty records?

Databases
21

that’s because you’re outputting the same thing on every row

look at how timeslice1 is defined :slight_smile:

22

Yes I know :slight_smile:

But i can’t think of a way to make it right :slight_smile: This is the query I have so far, but it gives me 0 records …

Select timeslice1 from (SELECT "2012-04-04 12:14:00" + INTERVAL 1 MINUTE AS timeslice1
FROM timeslices) as t where timeslice1 >= "2012-04-04 13:14:00" and timeslice1 <= "2012-04-04 14:14:00";

Greetings from Belgiüm.
Sven.

23

But to be honnest. I don’t think this will speed up the initial query. Because this bit of query

SELECT timeslice 
	FROM 	( 
		SELECT '2012-01-18 02:14' + INTERVAL (n-1) * 15 MINUTE AS timeslice
		FROM numbers 
		) 
	AS t
	WHERE timeslice <= '2012-01-19 19:00'

is lightning fast. It’s returns all the records in 16ms.

I really need a different approach here …

Greetings from Belgiüm.
Sven.

24

change this (from post #20) –

SELECT [COLOR="#FF0000"]'2012-04-04 12:14:00' [/COLOR]+ INTERVAL 15 MINUTE AS timeslice1
  FROM timeslices 
 WHERE timeslice >= '2012-04-04 13:14:00' 
   AND timeslice <= '2012-04-04 14:14:00'

to this –

SELECT [COLOR="#0000FF"]timeslice [/COLOR]+ INTERVAL 15 MINUTE AS timeslice1
  FROM timeslices 
 WHERE timeslice >= '2012-04-04 13:14:00' 
   AND timeslice <= '2012-04-04 14:14:00'
25

Hmmm, close but not close enough :slight_smile:

Look at the screenshot. It’s basicly the same. There is no “SELECT FROM (SELECT …” statement. It stills add the INTERVAL to the initial datetime value. And selects every record he can find until reaching the end datetime value.

Greetings from Belgiüm.
Sven.

26

and the problem with that is… ?

27

There is no interval of 15minutes … he’s just drilling down the records that resides in the table.