October 1st, 2010, 04:33 AM
I am creating an sql that gets me the number of records per 15 minutes. But i am stuck. I managed to get it every 1 minute which is not good enough. here is what ive got so far.. how can i alter it?
SELECT COUNT(*) as COUNTER,
CAST(CAST( EXTRACT(DAY FROM LOGS.DATETIME)||
EXTRACT(MONTH FROM LOGS.DATETIME)||
EXTRACT(YEAR FROM LOGS.DATETIME) AS DATE) ||
' ' ||
CAST( EXTRACT(HOUR FROM LOGS.DATETIME)||
EXTRACT(MINUTE FROM LOGS.DATETIME)||
'.00' AS TIME) AS TIMESTAMP) AS SPECIFIC_TIME
LOGS.DATETIME >= '2010-10-01 04:12:00' AND
LOGS.DATETIME <= '2010-10-01 10:12:59' AND
LOGS.SCANRESULT = 'OK'
ORDER BY SPECIFIC_TIME ASC
October 1st, 2010, 05:32 AM
why do you need this CAST thingy? it eats up a lot of CPU and i don't think you really need it
how about this?
SELECT COUNT(*) AS counter, "DATETIME" FROM logs
WHERE "DATETIME" BETWEEN datetime1 AND datetime2
GROUP BY "DATETIME"
and datetime1,datetime2 parameters should be in datetime format, or simply put the cast there [ like CAST '2010-10-01 04:12:00' AS DATETIME) ]
i bet it will be much faster, and for me it looks even easier
PS1: watch out that DATETIME filed name, because it is a reserver word so you should write it in UPPERCASE and between ""
PS2: you don't need order by if you have a group by clause
Originally Posted by kcauchi
October 1st, 2010, 05:47 AM
This is very simple :
COUNT(*) as COUNTER
FROM logs l
EXTRACT(YEAR FROM l.datetime),
EXTRACT(MONTH FROM l.datetime),
EXTRACT(DAY FROM l.datetime),
EXTRACT(HOUR FROM l.datetime),
div(EXTRACT(MINUTE FROM l.datetime),15)