SQL: How to select one record per day, assuming that each day contain more than 1 value MySQL

Question

I want to select records from '2013-04-01 00:00:00' to 'today' but, each day has lot of value, because they are saving each 15 minutes a value, so I want only the first or last value from each day.

Table schema:

CREATE TABLE IF NOT EXISTS `value_magnitudes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` float DEFAULT NULL,
  `magnitude_id` int(11) DEFAULT NULL,
  `sdi_belongs_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `reading_date` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1118402 ;

Bad SQL:

SELECT value FROM `value_magnitudes` WHERE `value_magnitudes`.`reading_date` BETWEEN '2013-04-01 00:00:00' AND '2013-04-02 00:00:00' AND (`value_magnitudes`.magnitude_id = 234) LIMIT 1
SELECT value FROM `value_magnitudes` WHERE `value_magnitudes`.`reading_date` BETWEEN '2013-04-02 00:00:00' AND '2013-04-03 00:00:00' AND (`value_magnitudes`.magnitude_id = 234) LIMIT 1
SELECT value FROM `value_magnitudes` WHERE `value_magnitudes`.`reading_date` BETWEEN '2013-04-03 00:00:00' AND '2013-04-04 00:00:00' AND (`value_magnitudes`.magnitude_id = 234) LIMIT 1
SELECT value FROM `value_magnitudes` WHERE `value_magnitudes`.`reading_date` BETWEEN '2013-04-04 00:00:00' AND '2013-04-05 00:00:00' AND (`value_magnitudes`.magnitude_id = 234) LIMIT 1
SELECT value FROM `value_magnitudes` WHERE `value_magnitudes`.`reading_date` BETWEEN '2013-04-05 00:00:00' AND '2013-04-06 00:00:00' AND (`value_magnitudes`.magnitude_id = 234) LIMIT 1
etc ...

I want all in one if possible...

Thank you a lot.

EDIT: I mean, I have a query per day, but I just want to make a single query from reading_date >= '2013-04-01 00:00:00'c

EDIT2: I have 64,260 records in that table.value_magnitudes and it takes sooooooooo long to excecute and response that query, and sometimes timeout conection.

Slut

Answer 1

To get the first entry for every date you can do

select * from value_magnitudes
where id in 
(
    SELECT min(id)
    FROM value_magnitudes
    WHERE magnitude_id = 234
    and date(reading_date) >= '2013-04-01'
    group by date(reading_date)
)

Sherbet

but if I do that I will do a query for each day till I reach today, is it possible in one query? thanks. – Slut

Yes, leave the date in the where condition. I updated my answer. – Sherbet

Aham, but I wanted starting at X date, to today, so it would be something like reading_date >= '2013-04-01 00:00:00' – Slut

Okay, thanks I indexed but still doesn't work, but anyway, thanks =D – Slut

use explain to see where the problem is. Like that: explain select * from ..... If you don't get it to work you can ask another question. – Sherbet

I think the problem is the date function making an index useless. – Sherbet

The only solution I see at the moment is splitting the date and time in 2 different columns. But maybe there is a better solution I don't know of. – Sherbet

I did a new question: #16481549 – Slut

Answer 2

select * from value_magnitudes
where id in 
(
   SELECT min(id)
   FROM value_magnitudes
   WHERE `value_magnitudes`.`reading_date` BETWEEN '$from_selected' AND '$to_selected' and (magnitude_id = 234) group by date(reading_date)
)

Ruthenian

Answer 3

using the above example - in case someone needs this - it's searching for invalid records where action should not be 0 if its the first row of the day:

select * from log
where action=0 AND id in 
(
    SELECT min(id)
    FROM log
    group by date(ts)
)

Nodus