CODEWITHSUNDEEP
mysqlsql
eduardo inoue
eduardo inoue

Reputation: 11

How to convert two varchar columns to time and substract the hours

I am trying to solve this problem and I really appreciate any help or direction.

I have a Mysql database with a events table where I have two columns with data type as varchar and the names are time_start and time_end

The values are stored as string in 24hours format: 16:00:00 for example

For example purpose image the result of select * from events is:

time_start time_end
13:00:00   14:00:00

How to convert those strings to time and substract (time_end - time_start) resulting in 1h

Tks for any help

Upvotes: 1

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270643

You can get the difference easily by using time_to_sec() or timestampdiff():

select time_to_sec(time(time_end)) - time_to_sec(time(time_start))

This gives the time in seconds. If you want integer hours, divide by 3600:

select floor( (time_to_sec(time(time_end)) - time_to_sec(time(time_start))) / (60 * 60)) as hours

Or, if you really just want to subtract the hour portion, you can do:

select left(time_end, 2) - left(time_start, 2)

MySQL will convert the values to numbers, because of the arithmetic operation.

Upvotes: 1

Related Questions