CODEWITHSUNDEEP
mysqldatetime
Alex van Es
Alex van Es

Reputation: 1181

MYSQL First and last datetime within a day

I have a table with 3 days of data (about 4000 rows). The 3 sets of data are all from a 30 minutes session. I want to have the start and ending time of each session.

I currently use this SQL, but it's quite slow (even with only 4000 records). The datetime table is indexed, but I think the index is not properly used because of the conversion from datetime to date. The tablelayout is fixed, so I cannot change any part of that. The query takes about 20 seconds to run.. (and every day longer and longer). Anyone have some good tips to make it faster?

select distinct
date(a.datetime) datetime,
(select max(b.datetime) from bike b where date(b.datetime) = date(a.datetime)),
(select min(c.datetime) from bike c where date(c.datetime) = date(a.datetime))
from bike a

enter image description here

Upvotes: 1

Views: 2458

Answers (2)

asantaballa
asantaballa

Reputation: 4048

Alex, warning, this in typed "freehand" so may have some syntax problems. But kind of shows what I was trying to convey. 

select distinct
date(a.datetime) datetime,
(select max(b.datetime) from bike b where b.datetime between date(a.datetime) and (date(a.datetime) + interval 1 day - interval 1 second)),
(select min(c.datetime) from bike c where c.datetime between date(a.datetime) and (date(a.datetime) + interval 1 day - interval 1 second))
from bike a

Instead of comparing date(b.datetime), it allows comparing the actual b.datetime against a range calculated form the a.datetime. Hopefully this helps you out and does not make things murkier.

Upvotes: 0

spencer7593
spencer7593

Reputation: 108480

Maybe I'm missing something, but...

Isn't the result returned by the OP query equivalent to the result from this query:

  SELECT DATE(a.datetime) AS datetime
       , MAX(a.datetime)  AS max_datetime
       , MIN(a.datetime)  AS min_datetime
    FROM bike a
   GROUP BY DATE(a.datetime)

Upvotes: 5

Related Questions