SQL, convert integer to subtract timestamps
Suppose we have a relation R (A, B), with A containing int values and B containing timestamps.
We need to calculate: (time in B in minutes) - (int to minutes)
.
Example:
(125, "2017-06-01 16:23:00")
16:23:00 = 983 min
125 = 125min
983 - 125 = 858min
The A elements represent minutes, my problem is converting the integer value >59
to hh:mm
, as it MAKETIME(hh, mm, ss)
only works in the range 0 to 59.
source to share
There is no need to convert timestamp column time to minutes at all.
Just do
SELECT B - INTERVAL A MINUTE;
If you really want time to subtract from do
SELECT TIME(B) - INTERVAL A MINUTE;
To leave a date detail unaffected:
SELECT CONCAT(DATE(B), ' ', TIME(B) - INTERVAL A MINUTE);
When you need minutes after:
SELECT HOUR(TIME(B) - INTERVAL A MINUTE) * 60 + MINUTE(TIME(B) - INTERVAL A MINUTE);
source to share