Mysql select data only if different

jwright

I want to see if this can be done in mysql since I know how to do it in php. I have a a table with three fields. id,date,time.

the time field is in minutes.

What I want is a mysql query that adds up all the time for a day and displays all days where the time does not add up to be 24hours or 1440 minutes.

This is for error checking. Since the program I am building requires that every day the total time = 24.

thanks

jwright

Sorry I left this out and this might make this impossible the time is stored as hours/minutes and they are stored in the field time in th eformat hour:minute.

I do not know if mysql can turn the 0:0 into minutes.

Anon

There's a Mysql function called TIME_TO_SEC that converts a time field to seconds, but it requires hh:mm:ss - so if all you have is mm:ss you could do something like:
TIME_TO_SEC(CONCAT('00:', name_of_time_field))

Victor Grey

[deleted]

Is the time stored in a true TIME field or a varchar field that just contains a string 'HH:MM'?

What you are looking for is a way to add all the times for each day and get those days that don't add up to 1440 minutes.

First let's get just the days:

SELECT *
FROM table
GROUP BY date;

Now for adding the times;
MySQL is not very picky about datatypes, and it will read hh:mm, h:mm, even hh:m as a time format and process it happily (I'm not sure wether this feature is 'way cool' or 'incredibly stupid') so you can use mysql time manipulation functions:

SELECT , SUM(HOUR(timefield))60+SUM(MINUTE(timefield)) AS totaltime
FROM table
GROUP by date

Finally to get only those days that don't have a totaltime of 1440, you can add the HAVING clause:

SELECT , SUM(HOUR(timefield))60+SUM(MINUTE(timefield)) AS totaltime
FROM table
GROUP by date
HAVING totaltime<1440