What data type should I use for a 'duration' attribute?

Question

I'm using phpmyadmin/MySQL to make a database.

It's for a plane/bus/train booking system.

I have a 'depart_time' attribute which is a time data type. In the same table I have a 'duration' attribute. Later on I will need to do multiplication on this duration attribute (depending on if it is train/bus/plane).

My question is - what would be the best data type for this duration attribute?

I thought about using a decimal type - but then the values in it won't represent the time exactly (e.g. 1.30 won't represent 1 and a half hrs, it would need to be 1.50 - if that makes sense). I also thought about using the time data type for this field as well, but I wasn't sure if multiplication would be possible on that?

I couldn't find any help after googling about multiplication on the time data type.

Hopefully this made sense, if you need anymore information then feel free to ask in the comments!

Thanks in advance!

Answer 1

Use an int and record durations in the smallest unit you're interested in. For example, if you need minute accuracy, store one and a half hours as 90 minutes. Formatting that value for display purposes is presentation logic, not the business of the database.