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 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.
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.