Btukfyl

the following sql statement delivers what it should. The min value in a defined timeframe.

select value, datetime from Schuppen 

where (value = (select min(value) from Schuppen 

    where (measure = 'temp') 

    and datetime between '2018-11-01 00:00:00' and '2018-11-02 00:00:00')) 

and datetime between '2018-11-01 00:00:00' and '2018-11-02 00:00:00';

When I use a variable, instead of the hardcoded dates, the statement hangs.

set @startdate = cast('2018-11-01 00:00:00' as datetime);   

select value, datetime from Schuppen 

where (value = (select min(value) from Schuppen 

    where (measure = 'temp') 

    and datetime between @startdate and '2018-11-02 00:00:00')) 

and datetime between '2018-11-01 00:00:00' and '2018-11-02 00:00:00';

I wonder why this statement does not work against my maria db version 10.1.26-MariaDB-0+deb9u1.

Tried via the mysql console.

mysql --user=XXXX --password=XXXX Outdoor-Air

...

Your MariaDB connection id is 194

Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1



...



MariaDB [Outdoor-Air]> set @startdate = cast('2018-11-01 00:00:00' as datetime);

Query OK, 0 rows affected (0.00 sec)

MariaDB [Outdoor-Air]> select @startdate;

+---------------------+

| @startdate          |

+---------------------+

| 2018-11-01 00:00:00 |

+---------------------+

1 row in set (0.01 sec)



MariaDB [Outdoor-Air]> select value, datetime from Schuppen where (value = (select min(value) from Schuppen where (measure = 'temp') and datetime between @startdate and '2018-11-02 00:00:00')) and datetime between '2018-11-01 00:00:00' and '2018-11-02 00:00:00';

Now the statement hangs.

SELECT  `value`, `datetime`

    FROM  Schuppen

    WHERE  measure = 'temp'

      AND  `datetime` >= '2018-11-01'

      AND  `datetime` <  '2018-11-01' + INTERVAL 1 DAY

    ORDER BY `value`  ASC

    LIMIT  1;

And this should help:

INDEX(measure, datetime, value)

One potential difference between this formulation and yours: This will show only one row if the lowest value occurs more than once on that day.

Ensure that both the queries, viz., SET and SELECT statements are running within a same session. @startdate will persist across a single session only.

You can also convert these two queries into a single query utilzing CROSS JOIN with Derived Table.

SELECT value,

       `datetime`

FROM   Schuppen

CROSS JOIN (SELECT @startdate := cast('2018-11-01 00:00:00' as datetime)) vars 

WHERE  ( value = (SELECT Min(value)

                  FROM   Schuppen

                  WHERE  ( measure = 'temp' )

                         AND `datetime` BETWEEN

                             @startdate AND '2018-11-02 00:00:00') )

       AND `datetime` BETWEEN '2018-11-01 00:00:00' AND '2018-11-02 00:00:00';