Thread: Firebird SQL

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    1
    Rep Power
    0

    Firebird SQL


    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

    FROM LOGS

    WHERE
    LOGS.DATETIME >= '2010-10-01 04:12:00' AND
    LOGS.DATETIME <= '2010-10-01 10:12:59' AND
    LOGS.SCANRESULT = 'OK'

    GROUP BY
    SPECIFIC_TIME

    ORDER BY SPECIFIC_TIME ASC
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15
    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
    AND scanresult='OK'
    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
    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

    FROM LOGS

    WHERE
    LOGS.DATETIME >= '2010-10-01 04:12:00' AND
    LOGS.DATETIME <= '2010-10-01 10:12:59' AND
    LOGS.SCANRESULT = 'OK'

    GROUP BY
    SPECIFIC_TIME

    ORDER BY SPECIFIC_TIME ASC
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    25
    Rep Power
    0
    Hi!

    This is very simple :

    Code:
    SELECT
        COUNT(*) as COUNTER
    FROM logs l
    WHERE ...
    GROUP BY
        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)

IMN logo majestic logo threadwatch logo seochat tools logo