Geek asks and answers

MySQL time interval query with MySQL

I have a table in a database that stores log data over time. There can be a million lines in db for one day. Times have no regular interval. It has several indexes, including time. I want to create a query that will return a set of rows with one row for a period of time. For example, I could make a request to return 1 row every 15 minutes during the day. This will return 24 * 60 = 96 lines. Each returned row will in fact be the closest row in db to the requested interval (since the data in the database will not equal the requested interval).

I don’t understand how to do this. I can’t just query all the rows for a specific set of indices and a time interval, since it would load more than a gigabyte of data into memory, which is too slow. Is there an efficient way to do this using SQL. I am using a MySQL database. I would be open to changing table indexes / etc ...

TIME

11:58
12:03
12:07
12:09
12:22
12:27
12:33
12:38
12:43
12:49
12:55

If I wanted to request this for a 15-minute interval from 12:00 to 1:00, I would return:

11:58 (nearest 12:00)
12:09 (nearest 12:15)
12:27 (nearest 12:30)
12:43 (nearest 12:45)
12:55 (nearest 1:00)

If this simplifies, I can also save time as a number (i.e. ms since 1970). In the above query, this will be an interval of 900,000 ms.

+3
source share
3 answers

So, I thought something like:

SELECT 
  MIN(timeValue)
FROM e
GROUP BY (to_seconds(timeValue) - (to_seconds(timeValue) % (60 * 5)))

.. , MIN (timeValue) . , , 5 , .

. SQL Fiddle

Andiry, : (http://sqlfiddle.com/#!2/bb870/6)

SELECT MIN(t)
FROM e
GROUP BY to_seconds(t) DIV (60 * 5)

: (http://sqlfiddle.com/#!2/bb870/7)

SELECT MIN(t)
FROM e
GROUP BY to_seconds(t) - (to_seconds(t) % (60 * 5))

- , ?

+4

. , - , , , - :

$startTime = mktime(12, 0);
$endTime = mktime(13, 0);
$queries = array();
for ($i = $startTime; $i <= $endTime; $i += 900)
    $queries[] = "SELECT MAX(timeValue) FROM table1 WHERE timeValue < '". date("G:i", $i) ."'";

$query = implode("\nUNION\n", $queries);

, , PHP. , , :

SELECT MAX(timeValue) FROM table1 WHERE timeValue < '12:00'
UNION
SELECT MAX(timeValue) FROM table1 WHERE timeValue < '12:15'
UNION
SELECT MAX(timeValue) FROM table1 WHERE timeValue < '12:30'
UNION
SELECT MAX(timeValue) FROM table1 WHERE timeValue < '12:45'
UNION
SELECT MAX(timeValue) FROM table1 WHERE timeValue < '13:00'

, < 100% , , unix ( ms 1970 , ), , / .

0

I think the use of functions is quite simple, and I did not notice big performance implications, although the cursor probably transforms better, depending on how many lines exist between them.

CREATE TABLE TEST_TIMES (EventTime datetime)
-- skipping INSERTS of your times

CREATE FUNCTION fn_MyTimes ( @StartTime datetime, @EndTime datetime, @Minutes int )
    RETURNS @TimeTable TABLE (TimeValue datetime)
AS BEGIN
    DECLARE @CurrentTime datetime
    SET @CurrentTime = @StartTime
    WHILE @CurrentTime <= @EndTime
    BEGIN
        INSERT INTO @TimeTable VALUES (@CurrentTime)
        SET @CurrentTime = DATEADD(minute, @Minutes, @CurrentTime)
    END
    RETURN
END

CREATE FUNCTION fn_ClosestTime ( @CheckTime datetime )
    RETURNS datetime
AS BEGIN
    DECLARE @LowerTime datetime, @HigherTime datetime

    SELECT @LowerTime = MAX(EventTime)
    FROM TEST_TIMES
    WHERE EventTime <= @CheckTime

    SELECT @HigherTime = MAX(EventTime)
    FROM TEST_TIMES
    WHERE EventTime >= @CheckTime

    IF @LowerTime IS NULL RETURN @HigherTime -- both null?  then null
    IF @HigherTime IS NULL RETURN @LowerTime

    IF DATEDIFF(ms, @LowerTime, @CheckTime) < DATEDIFF(ms, @CheckTime, @HigherTime)
        RETURN @LowerTime
    RETURN @HigherTime
END

SELECT TimeValue, dbo.fn_ClosestTime(TimeValue) as ClosestTime
FROM fn_MyTimes('2012-05-17 12:00', '2012-05-17 13:00', 15)

Results:

TimeValue               ClosestTime
----------------------- -----------------------
2012-05-17 12:00:00.000 2012-05-17 11:58:00.000
2012-05-17 12:15:00.000 2012-05-17 12:09:00.000
2012-05-17 12:30:00.000 2012-05-17 12:27:00.000
2012-05-17 12:45:00.000 2012-05-17 12:43:00.000
2012-05-17 13:00:00.000 2012-05-17 12:55:00.000
0
source

More articles:


All Articles