Anthony Paul Willis Anthony Paul Willis - 23 days ago 6
MySQL Question

Inserting a percentage of a TIME type value in MySQL

I am running a MySQL that needs to insert a TIME type value into a table.

Currently it goes

INSERT INTO my_table (time_value) VALUES ('05:00:00')


What can I put around the '05:00:00' that it will be a specified % of that value (eg if it were 20% it would be inserted at '01:00:00')

Answer

You can use MySQL's time_to_sec() function to get the time in seconds, perform the calculation, then convert the seconds back to time using sec_to_time() function:

INSERT INTO my_table (time_value) VALUES (sec_to_time(time_to_sec('05:00:00') * 0.2))

Or just do the same calculation in php and just insert the result into MySQL. See Convert time in HH:MM:SS format to seconds only? question here on SO for time to second conversion.