santa santa - 27 days ago 8
PHP Question

Calculate number of hours inside the query

I have a table that stores hours when a business is open.

id | open | closed

The two columns that store hours are type
and the value looks something like this:

800 for 8:00 AM

2200 for 10:00 PM

0 for midnight

I need to calculate the number of hours the business stays open. For example:

Open: 1000
Closed: 2200
Hours: 12

Can at least a part of this be done inside a query? Or shall I output the data and then do all the computations?

Answer Source

Storing it as a smallint is problematic.

You can substract 2200 - 1000 = 1200, then divide by 1000 to get number of hours, but you'll get in trouble when having 2200 - 1030 = 1130. It's doable, but why to make your life difficult?

You can keep the data as time field and just use timediff (

SELECT id, open, closed, TIMEDIFF(closed, open) AS hours FROM ...