What kind is appropriate for field that saves hours in mysql?

Question

I need to save a field that saves hours in the database mysql. It’s a service record where I need to store the amount of hours of that particular service. When the service scheduling occurs at 11:00 for example, the system adds up time spent for that service + the time of the service start. For example, the service lasts one hour, so the schedule will be available only from 12:00. So, what kind of camp duracao_servico, which will be added to the scheduling time?

Answer 1

In the scenario described, it is semantically correct to store in numerical format.

Note that you are not storing one timing in time, and yes a amount. If it were storing a temporal position, it would be a more likely case to use the type of time (time) specific to the DB.

As it will store an absolute number of hours or minutes, the numeric field makes things very simple when using DB itself to calculate the final time:

SELECT inicio + INTERVAL duracaoMinutos MINUTE AS final

Or even

DATE_ADD( inicio, INTERVAL duracaoMinutos MINUTE ) 

See working on Sqlfiddle.

If you prefer, use HOUR for "busy hours".

Note: If store as time, "even works", but be careful with the output format in this case. It might be a little different than expected if you try to use the +:

Example with time in Sqlfiddle.

Answer 2

According to the Mysql documentation you have the following types to work with dates:

• Date
• Time
• Datetime
• Timestamp
• Year

Below how the representation of data types is made:

Type | Value

DATE - '0000-00-00'

TIME - '00:00:00'

DATETIME - '0000-00-00 00:00:00'

TIMESTAMP - '0000-00-00 00:00:00'

YEAR - 0000

You can use the type time, making their subtraction between them.

Or you can use the type datetime, which can also save the date (if this is an option). Then subtract between the two fields just use the timediff function:

SELECT TIME_TO_SEC(TIMEDIFF('2016-10-12 12:01:00', '2016-10-12 12:00:00')) diff;

---

References:

Mysql documentation (website)

Mysql: how to get the Difference between two timestamps in Seconds

Answer 3

According to the manual (http://dev.mysql.com/doc/refman/5.7/en/time.html) Time field is specific to save hours/minutes/seconds in the following format:

HH:MM:SS

You can recover the data in another format using:

SELECT TIME_FORMAT(foo_hour, '%H:%i') FROM bar

If you also want to save the date, you can put both in a datetime field and use the DATE_FORMAT function to get only the date or only the time as per: http://www.w3schools.com/sql/func_date_format.asp

Answer 4

Work with the guy Team and so can manipulate exact hours.