Date & Time types
In different SQL dialects, date and time types may differ, but the most common are DATE, TIME, DATETIME, TIMESTAMP, and INTERVAL. This article follows MySQL's implementation of these types to show other types and different data ranges for them in other SQL dialects.
The DATE type is used for storing a date that consists of a year, a month, and a day (without the time) in the 'YYYY-MM-DD' format. Use it to store the dates like 2020-12-31 (the 31st of December 2020) or 1996-11-06 (the 6th of November 1996). This type supports any date ranging from 1000-01-01 to 9999-12-31.
To store only the time without the date, use the TIME type. The TIME type stores hours, minutes, and seconds in the 'hh:mm:ss' format. Use it to store the time of the day or to store the time that will represent the interval between two dates because the range supported by this type is very wide, from -838:59:59 to 838:59:59. In MySQL, both '00:00:00' and '-455:34:34' are valid values of the TIME type. Use values like '54:45' as TIME values, but be careful: this value will be interpreted by MySQL as '54:45:00' not as '00:54:45'.
The DATETIME type stores the date and time in the 'YYYY-MM-DD hh:mm:ss' format. It supports the range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. The TIMESTAMP is another type used to store both date and time, but in MySQL, its range is more narrow. The range of TIMESTAMP is from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
Another interesting data type for time storage is INTERVAL. Use it to store the interval between two dates. There are two classes of the INTERVAL type: INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. The first one stores the difference between two dates in years and months, while the second one will be useful if you need to store the difference in days, hours, minutes, and seconds. This data type can be met in the Oracle SQL dialect.
Current date & time
To use the current date and time while working with data, MySQL provides several functions to get time, date, or both.
Use the CURDATE() or CURRENT_DATE() functions to get the current date. These two functions are fully equivalent. To select the current date, use the following query:
Likewise, to get the current time use CURRENT_TIME() or CURTIME() functions. To select the current time, use the following function:
To select both date and time, apply CURRENT_TIMESTAMP() function:
Date & time difference
To calculate the difference between two dates, utilize the DATEDIFF(first_date, second_date) function. In the MySQL dialect, the DATEDIFF() function yields the number of days between the two dates. However, various other SQL dialects offer the flexibility to specify the desired units for the difference, such as years, months, seconds, and more.
For example, the query below in MySQL will return 5 as a result:
Caution! A negative number can result from the query execution when first_date is smaller than second_date.
In MySQL, use TIMEDIFF(first_time, second_time) to get the difference between two TIME values.
Level up your tech skills and advance your career
• Wide range of SQL and Databases tracks
• Study at your own pace with your personal study plan
• Focus on practice and real-world experience