{"id":4091,"date":"2015-11-06T20:49:09","date_gmt":"2015-11-07T04:49:09","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=4091"},"modified":"2023-10-10T02:30:31","modified_gmt":"2023-10-10T09:30:31","slug":"mysql-time","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-time\/","title":{"rendered":"MySQL TIME Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the MySQL TIME<\/code> data type and how to use temporal functions to manipulate time data effectively.<\/p>\n\n\n\n

Introduction to MySQL TIME data type<\/h2>\n\n\n\n

MySQL uses the 'HH:MM:SS'<\/code> format for querying\u00a0and displaying a time value that represents a time of day, which is within 24 hours.\u00a0<\/p>\n\n\n\n

To represent a time interval<\/a> between two events, MySQL uses the 'HHH:MM:SS'<\/code> format, which is larger than 24 hours.<\/p>\n\n\n\n

To define a TIME<\/code>column, you use the following syntax:<\/p>\n\n\n

column_name TIME;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following snippet defines a column named start_at<\/code> with TIME<\/code> data type.<\/p>\n\n\n
start_at TIME;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
A TIME<\/code> value ranges from -838:59:59<\/code> to 838:59:59<\/code>. In addition, a TIME<\/code> value can have a fractional seconds part that is up to microseconds precision (6 digits).\u00a0<\/p>\n\n\n\n
To define a column whose data type is TIME<\/code> with a fractional-second precision part, you use the following syntax:<\/p>\n\n\n
column_name TIME(N);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following defines a column with TIME<\/code> data type including 3 digits of fractional seconds.<\/p>\n\n\n
begin_at TIME(3);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
A TIME<\/code> value takes 3 bytes for storage. If a TIME<\/code> value includes fractional second precision, it will take additional bytes based on the number of digits of the fractional second precision. <\/p>\n\n\n\n
The following table illustrates the storage required for fractional second precision.<\/p>\n\n\n\n
Fractional Second Precision<\/strong><\/td>Storage (BYTES)<\/strong><\/td><\/tr>
0<\/td>0<\/td><\/tr>
1, 2<\/td>1<\/td><\/tr>
3, 4<\/td>2<\/td><\/tr>
5, 6<\/td>3<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
For example, TIME<\/code> and TIME(0)<\/code> takes 3 bytes. TIME(1)<\/code> and TIME(2)<\/code> takes 4 bytes (3  + 1); TIME(3)<\/code> and TIME(6)<\/code> take 5 and 6 bytes.<\/p>\n\n\n\n
MySQL TIME data type example<\/h2>\n\n\n\n
Let’s take a look at an example of using the TIME<\/code> data type for columns in a table.<\/p>\n\n\n\n
First, create a new table<\/a> named tests<\/code>\u00a0that consists of four columns: id<\/code>, name<\/code>, start_at<\/code>, and end_at<\/code>. The data types of the start_at<\/code> and end_at<\/code> columns are TIME<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> tests (\n    id<\/span> INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n    name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    start_at TIME<\/span>,\n    end_at TIME<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> tests(name<\/span>,start_at,end_at)\nVALUES<\/span>('Test 1'<\/span>, '08:00:00'<\/span>,'10:00:00'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> \n    name<\/span>, start_at, end_at\nFROM<\/span>\n    tests;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------+----------+----------+\r\n| name   | start_at | end_at   |\r\n+--------+----------+----------+\r\n| Test 1<\/span> | 08<\/span>:00<\/span>:00<\/span> | 10<\/span>:00<\/span>:00<\/span> |\r\n+--------+----------+----------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL TIME literals<\/h2>\n\n\n\n
MySQL recognizes various time formats besides the 'HH:MM:SS'<\/code> format that we mentioned earlier.<\/p>\n\n\n\n
MySQL allows you to use the 'HHMMSS'<\/code> format without delimiter ( : ) to represent time value. For example, '08:30:00'<\/code> and '10:15:00'<\/code> can be rewritten as '083000'<\/code> and '101500'<\/code>.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> tests(name<\/span>,start_at,end_at)\nVALUES<\/span>('Test 2'<\/span>,'083000'<\/span>,'101500'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The tests table will have the following rows:<\/p>\n\n\n
+--------+----------+----------+\r\n| name   | start_at | end_at   |\r\n+--------+----------+----------+\r\n| Test 1<\/span> | 08<\/span>:00<\/span>:00<\/span> | 10<\/span>:00<\/span>:00<\/span> |\r\n| Test 2<\/span> | 08<\/span>:30<\/span>:00<\/span> | 10<\/span>:15<\/span>:00<\/span> |\r\n+--------+----------+----------+\r\n2<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
However, 108000<\/code> is not a valid time value because 80<\/code> does not represent the correct minute. In this case, MySQL will raise an error if you try to insert an invalid time value into a table.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> tests(name<\/span>,start_at,end_at)\nVALUES<\/span>('Test invalid'<\/span>,'083000'<\/span>,'108000'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issued the following error message after executing the above statement.<\/p>\n\n\n
Error Code: 1292. Incorrect time value: '108000' for column 'end_at' at row 1<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In addition to the string format, MySQL accepts the HHMMSS<\/code> as a number that represents a time value. You can also use SS<\/code>, MMSS<\/code>. For example, instead of using '082000'<\/code>, you can use 082000<\/code> as follows:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> tests(name<\/span>,start_at,end_at)\nVALUES<\/span>('Test 3'<\/span>,082000<\/span>,102000<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
+----+--------+----------+----------+\r\n| id | name   | start_at | end_at   |\r\n+----+--------+----------+----------+\r\n|  1<\/span> | Test 1<\/span> | 08<\/span>:00<\/span>:00<\/span> | 10<\/span>:00<\/span>:00<\/span> |\r\n|  2<\/span> | Test 2<\/span> | 08<\/span>:30<\/span>:00<\/span> | 10<\/span>:15<\/span>:00<\/span> |\r\n|  3<\/span> | Test 3<\/span> | 08<\/span>:20<\/span>:00<\/span> | 10<\/span>:20<\/span>:00<\/span> |\r\n+----+--------+----------+----------+\r\n3<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
For the time interval, you can use the 'D HH:MM:SS'<\/code> format where D<\/code> represents days with a range from 0 to 34. A more flexible syntax is 'HH:MM'<\/code>, 'D HH:MM'<\/code>, 'D HH'<\/code>, or 'SS'<\/code>.<\/p>\n\n\n\n
If you use the delimiter :<\/code>, you can use one digit to represent hours, minutes, or seconds. For example, 9:5:0<\/code> can be used instead of '09:05:00'<\/code>.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> tests(name<\/span>,start_at,end_at)\nVALUES<\/span>('Test 4'<\/span>,'9:5:0'<\/span>,100500<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
+----+--------+----------+----------+\r\n| id | name   | start_at | end_at   |\r\n+----+--------+----------+----------+\r\n|  1<\/span> | Test 1<\/span> | 08<\/span>:00<\/span>:00<\/span> | 10<\/span>:00<\/span>:00<\/span> |\r\n|  2<\/span> | Test 2<\/span> | 08<\/span>:30<\/span>:00<\/span> | 10<\/span>:15<\/span>:00<\/span> |\r\n|  3<\/span> | Test 3<\/span> | 08<\/span>:20<\/span>:00<\/span> | 10<\/span>:20<\/span>:00<\/span> |\r\n|  4<\/span> | Test 4<\/span> | 09<\/span>:05<\/span>:00<\/span> | 10<\/span>:05<\/span>:00<\/span> |\r\n+----+--------+----------+----------+\r\n4<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Useful MySQL TIME functions<\/h2>\n\n\n\n
MySQL provides several useful temporal functions for manipulating TIME<\/code> data.<\/p>\n\n\n\n
1) Getting the current time<\/h3>\n\n\n\n
To get the current time of the database server, you use the CURRENT_TIME<\/code> function. The CURRENT_TIME<\/code> function returns the current time value as a string ( 'HH:MM:SS'<\/code>) or a numeric value ( HHMMSS<\/code>) depending on the context where the function is used.<\/p>\n\n\n\n
The following statements illustrate the CURRENT_TIME<\/code> function in both string and numeric contexts:<\/p>\n\n\n
SELECT<\/span> \n    CURRENT_TIME<\/span>() AS<\/span> string_now,\n    CURRENT_TIME<\/span>() + 0<\/span> AS<\/span> numeric_now;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
2) Adding and Subtracting time from a TIME value<\/h3>\n\n\n\n
To add a TIME<\/code> value to another TIME<\/code> value, you use the ADDTIME<\/code> function. To subtract a TIME<\/code> value from another TIME<\/code> value, you use  the SUBTIME<\/code> function.<\/p>\n\n\n\n
The following statement adds and subtracts 2 hours 30 minutes to and from the current time.<\/p>\n\n\n
SELECT<\/span> \n    CURRENT_TIME<\/span>(),\n    ADDTIME<\/span>(CURRENT_TIME<\/span>(), 023000<\/span>),\t\n    SUBTIME<\/span>(CURRENT_TIME<\/span>(), 023000<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
+----------------+---------------------------------+---------------------------------+\r\n| CURRENT_TIME() | ADDTIME(CURRENT_TIME(), 023000<\/span>) | SUBTIME(CURRENT_TIME(), 023000<\/span>) |\r\n+----------------+---------------------------------+---------------------------------+\r\n| 16<\/span>:23<\/span>:31<\/span>       | 18<\/span>:53<\/span>:31<\/span>                        | 13<\/span>:53<\/span>:31<\/span>                        |\r\n+----------------+---------------------------------+---------------------------------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In addition, you can use the TIMEDIFF()<\/code> function to get a difference between two TIME<\/code> values.<\/p>\n\n\n
SELECT<\/span> \n    TIMEDIFF<\/span>(end_at, start_at)\nFROM<\/span>\n    tests;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Formatting MySQL TIME values<\/h3>\n\n\n\n
Although MySQL uses 'HH:MM:SS'<\/code> when retrieving and displaying a TIME<\/code>\u00a0value, you can display the\u00a0TIME<\/code> value in your preferred way using the\u00a0TIME_FORMAT<\/code> function.<\/p>\n\n\n\n
The TIME_FORMAT<\/code> function is like the DATE_FORMAT<\/a><\/code> function except that the TIME_FORMAT<\/code> function is used to format a TIME<\/code> value only.<\/p>\n\n\n\n
See the following example.<\/p>\n\n\n
SELECT<\/span> \n    name<\/span>,\n    TIME_FORMAT<\/span>(start_at, '%h:%i %p'<\/span>) start_at,\n    TIME_FORMAT<\/span>(end_at, '%h:%i %p'<\/span>) end_at\nFROM<\/span>\n    tests;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+--------+----------+----------+\r\n| name   | start_at | end_at   |\r\n+--------+----------+----------+\r\n| Test 1<\/span> | 08<\/span>:00<\/span> AM | 10<\/span>:00<\/span> AM |\r\n| Test 2<\/span> | 08<\/span>:30<\/span> AM | 10<\/span>:15<\/span> AM |\r\n| Test 3<\/span> | 08<\/span>:20<\/span> AM | 10<\/span>:20<\/span> AM |\r\n| Test 4<\/span> | 09<\/span>:05<\/span> AM | 10<\/span>:05<\/span> AM |\r\n+--------+----------+----------+\r\n4<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In the time format string above:<\/p>\n\n\n\n
    \n
  •  %h<\/code> means two-digit hours from 0 to 12.<\/li>\n\n\n\n
  •  %i <\/code>means two-digit minutes from 0 to 60.<\/li>\n\n\n\n
  •  %p<\/code> means AM or PM.<\/li>\n<\/ul>\n\n\n\n
    4) Extracting hour, minute, and second from a TIME value<\/h3>\n\n\n\n
    To extract the hour, minute, and second from a TIME<\/code> value, you use HOUR<\/code>, MINUTE<\/code>, and SECOND<\/code> functions as follows:<\/p>\n\n\n
    SELECT \n  start_at, \n  hour(start_at) hour, \n  minute(start_at) minute, \n  second(start_at) second \nFROM \n  tests;<\/code><\/span><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +----------+------+--------+--------+\r\n| start_at | hour | minute | second |\r\n+----------+------+--------+--------+\r\n| 08<\/span>:00<\/span>:00<\/span> |    8<\/span> |      0<\/span> |      0<\/span> |\r\n| 08<\/span>:30<\/span>:00<\/span> |    8<\/span> |     30<\/span> |      0<\/span> |\r\n| 08<\/span>:20<\/span>:00<\/span> |    8<\/span> |     20<\/span> |      0<\/span> |\r\n| 09<\/span>:05<\/span>:00<\/span> |    9<\/span> |      5<\/span> |      0<\/span> |\r\n+----------+------+--------+--------+\r\n4<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    5) Getting UTC time value<\/h3>\n\n\n\n
    To get the UTC time, you use UTC_TIME<\/code> function as follows:<\/p>\n\n\n
    SELECT<\/span> \n   CURRENT_TIME<\/span>(), \n   UTC_TIME();<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +----------------+------------+\r\n| CURRENT_TIME() | UTC_TIME() |\r\n+----------------+------------+\r\n| 16<\/span>:24<\/span>:43<\/span>       | 09<\/span>:24<\/span>:43<\/span>   |\r\n+----------------+------------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this tutorial, we have been covered a lot about MySQL TIME<\/code> data type\u00a0and some commonly used temporal functions for manipulating TIME<\/code> values.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Notice that we use 'HH:MM:SS'<\/code> as the literal time value in the INSERT<\/a><\/code> statement. Let’s examine all the valid\u00a0time literals that MySQL can recognize.<\/p>\n\n\n\n
Third, query data<\/a> from the tests<\/code> table.<\/p>\n\n\n
Second, insert a row<\/a> into the tests<\/code> table.<\/p>\n\n\n
In this syntax, N<\/code> is an integer<\/a> that represents the fractional part, which is up to 6 digits.<\/p>\n\n\n\n