MySQL – calculating minutes between start and end times

I made a simple table in MySQL to use for start and end times (i.e., for billing a job):

CREATE TABLE `billing` (
`start` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`stop` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`total` int(11) NOT NULL default ‘0’
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Took me awhile to figure out how to get the actual minutes, but by converting the datetime to unix_timestamp first (then dividing by 60) it worked – yay!

UPDATE billing SET total = (unix_timestamp(stop) – unix_timestamp(start)) / 60

To quickly get the total minutes for the column:

SELECT SUM(total) FROM billing

To show hours:

SELECT SUM(total / 60) FROM billing

Leave a Reply

Your email address will not be published. Required fields are marked *