I need to save a field that stores hours in the mysql database.
It's a service register where I need to save the amount of hours for that particular service. When the service scheduling occurs at 11:00 am for example, the system adds hour spent for that service + the time of the start of the service. For example, the service lasts one hour, so the schedule will only be available after 12:00.
So what kind of field duracao_servico , which will be added at the time of the schedule?
Which type is appropriate for field that saves hours in mysql?
1
asked by anonymous 12.10.2016 / 17:27
4 answers
7
In the scenario described, it is semantically correct to store in numeric format.
Note that you are not storing a moment in time, but rather a amount . If you were storing a temporary position, it would be a more likely case to use the specific time type ( time ) of the DB.
Since you are going to store an absolute number of hours or minutes, the numeric field makes it very simple to use the DB itself to calculate the end 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 "full" hours.
Note: If you store as time , "until it works", but be careful about the output format in this case. It may be slightly different than expected if you try to use + :
Example with time in SQLFiddle .
12.10.2016 / 19:15
3
According to the MySQL documentation you have the following types to work with dates:
-
Date -
Time -
Datetime -
Timestamp -
Year
Below is the representation of the data types:
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 type time , subtracting them from one another.
Or you can use type datetime , which you can also save the date (if this is an option). So doing the subtraction 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: how to get the difference between two timestamps in seconds
12.10.2016 / 18:45
0
According to the manual ( link ) the 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 want to save the date as well, you can put the two in a datetime field and use the DATE_FORMAT function to get only the date or only the conforming time: link
12.10.2016 / 18:58
-1
Work with type Time and you can handle exact times.
12.10.2016 / 18:24