What Is the Data Type of Time in MySQL?


Angela Bailey

In MySQL, the data type used to store time values is TIME. The TIME data type allows you to store and manipulate time values in a specific format.

Understanding the TIME Data Type

The TIME data type is used to represent time values in a 24-hour format. It stores the hours, minutes, seconds, and microseconds of a particular time. The syntax for declaring a column with the TIME data type is as follows:

column_name TIME(fsp)

Here, column_name is the name of the column, and fsp (fractional seconds precision) specifies the number of digits for fractional seconds precision. The default value for fsp is 0 if not specified.

The Range of Time Values

The range of values that can be stored in a TIME column depends on the number of bytes allocated to it. By default, MySQL allocates 3 bytes for storing time values. This allows you to store time values in the range from ‘-838:59:59’ to ‘838:59:59’ (HH:MM:SS).

If you need to store more precise time values with fractional seconds, you can increase the number of bytes allocated by specifying a larger fsp. For example, if you specify fsp=2, MySQL will allocate 4 bytes for storing time values with up to two decimal places for fractional seconds.

Working with Time Values

In MySQL, various functions are available to work with time values. Here are some commonly used functions:

  • NOW(): Returns the current time as a value in ‘YYYY-MM-DD HH:MM:SS’ format.
  • TIME(): Extracts the time part from a given datetime or timestamp value.
  • TIMESTAMPDIFF(unit, start_time, end_time): Calculates the difference between two time values in the specified unit (e.g., seconds, minutes, hours).
  • ADDTIME(time_value, time_interval): Adds a time interval to a given time value.
  • SUBTIME(time_value, time_interval): Subtracts a time interval from a given time value.

Example Usage

Let’s see an example that demonstrates the usage of these functions:

SELECT NOW(); -- Returns current date and time
SELECT TIME('2022-12-31 23:59:59'); -- Returns only the time part
SELECT TIMESTAMPDIFF(MINUTE, '2022-01-01 00:00:00', '2022-01-01 01:30:00'); -- Returns 90
SELECT ADDTIME('12:30:15', '02:15:45'); -- Returns '14:46:00'
SELECT SUBTIME('12:30:15', '02:15:45'); -- Returns '10:14:-30'

The above queries demonstrate how you can use these functions to manipulate and calculate with time values in MySQL.


The TIME data type in MySQL allows you to store and work with time values efficiently. By understanding its syntax and available functions, you can effectively handle various operations involving time within your database.

Discord Server - Web Server - Private Server - DNS Server - Object-Oriented Programming - Scripting - Data Types - Data Structures

Privacy Policy