There is a manual page on Time and Date functions.
The best way to compare date and time and get minutes back is probably to use one of the following:
SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)
Note that while TIMEDIFF
works with times, TIMESTAMPDIFF
requires a date component - and the values are switched around for TIMESTAMPDIFF
.
You can return hours with either of these:
SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)
Note that when using DATEDIFF
, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).
To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:
SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)
This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.
This should do the trick for you:
DELIMITER $$
DROP TRIGGER IF EXISTS `employee_INSERT` $$
CREATE TRIGGER `employee_INSERT`
AFTER INSERT ON `employee`
FOR EACH ROW
BEGIN
INSERT INTO employee_tools (Id, Tool)
SELECT new.Id, tools.Tool_Name
FROM tools
WHERE tools.Division = new.Division;
END $$
DELIMITER ;
Best Answer
To go along with @ypercube's comment that
CURRENT_TIMESTAMP
is stored as UTC but retrieved as the current timezone, you can affect your server's timezone setting with the --default_time_zone option for retrieval. This allows your retrieval to always be in UTC.By default, the option is 'SYSTEM' which is how your system time zone is set (which may or may not be UTC!):
You can set this dynamically:
Or permanently in your my.cnf:
Restart your server, and you will see the change: