MySQL | TIME
Start your free 7-days trial now!
MySQL's TIME type is used when we do not need information about the date. It has the following syntax:
'hh:mm:ss' ('hhh:mm:ss' --format for large hours values)
A few useful points to note:
The supported range is
'-838:59:59'to'838:59:59'.The possible hour value is greater than 24 as time values can also be used to represent time intervals.
Values that are outside the valid
TIMErange but are otherwise valid are converted to the closer of the upper or lowerTIMErange limit.Invalid
TIMEvalues are converted to'00:00:00'
Assumption of abbreviated values
If you do not provide the TIME value in full, the following assumptions are made when converting:
Colon | Example value | Assumption | Converted value |
|---|---|---|---|
YES |
| Colons are taken to represent time of day |
|
NO |
| Represent time interval. Two rightmost digits represent seconds. |
|
Fractional Seconds
A TIME value can store a fractional seconds portion for microseconds (up to 6 digits precision).
'hh:mm:ss.ffffff'
The delimiter between seconds and microseconds must be a dot (.)
For the fractional seconds to be stored, the column must be defined to accept fractional seconds up to specific precision. The general syntax is as follows:
column_name type_name(fsp);
TIME, DATETIME, TIMESTAMP data types can hold fractional second information:
CREATE TABLE sample_table (column1 TIME(3), column2 DATETIME(6), column3 TIMESTAMP(1));
