MySQL Time - Formats

There are three different types of time data types in MySQL: TIME, DATETIME, and TIMESTAMP. If you would like to learn more about DATETIME and TIMESTAMP, then check out our MySQL Date section, as we've covered them there. This lesson will just be covering the basics of using TIME.

MySQL Time - TIME

First you need to create a MySQL table with a TIME type. We have one already created if you want to use it: timeplayground.sql.

The TIME data type can be used to store actual times as well as the amount of time between two points in time (like the time between now and the weekend) that may sometimes be larger than 23 hours. H - Hour; M - Minute; S - Second.

Standard format: HH:MM:SS

Extended hour format: HHH:MM:SS

Time Range: -838:59:50 to 838:59:59

When manually entering a time into MySQL it is highly recommended that you
use the exact format show above. MySQL allows for many different ways to enter
a time, but they don't always behave as you would expect. Using the standard/extended
format we have shown above will help you avoid annoying problems.

Below we have entered 3 manual times into MySQL. The first is done in the recommended
format, the second is a shorthand version of the first and the final example is
outside the allowed time range.

Finished timeplayground.sql Display:

dp_name

dp_time

TIME: Manual Time

12:10:00

TIME: Manual Time

00:12:10

TIME: Manual Time

838:59:59

TIME: Auto NOW()

14:30:36

Our first manual time was handled just fine, but our second one did not. MySQL interpreted 1210 as MM:SS instead of HH:MM as we assumed. This is
why it's best to use the formats we've described at the beginning.

The third manual entry was changed from 978:31:12 to 838:59:59, so that it would
be within TIME's range.