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.

t1
----------------------
id | open | closed


The two columns that store hours are type
smallint
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 (https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timediff)

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