Summary for Date and Time Functions

The SQL function CAST() is a means to cast a value of any type to a value of a specified target data type. With this, CAST() is also able to modify temporal values. This way of using CAST() to modify values of temporal data types works reasonably well for basic use cases. For example, casting a value of type DATE to a value of type YEAR extracts the year of the given date. The result is equivalent to applying the SQL function YEAR() to a DATE. However, we cannot accomplish more involved use cases using the CAST() function.

Creation

One common way of creating a date value is using a literal string that provides a date in a particular format. If we are up to a custom date format, we can also refer to STR_TO_DATE(string, format), which takes a literal string specifying a date and a format that describes the string’s formatting. Instead of manually providing a date, we can also automatically retrieve the current date using the function CURDATE(). For values of time and combinations of date and time, the possibilities are similar with MAKETIME(hour, minute, second) and CURTIME(), as well as NOW(), respectively.

Access

After creation, MySQL provides a series of functions that easily enable us to access components of temporal values. For dates, there are corresponding functions to extract the DAY(), the MONTH(), and the YEAR(). Likewise, the components of time can be accessed with MICROSECOND(), SECOND(), MINUTE(), and HOUR(). For combinations of date and time, MySQL supports us with DATE(), TIME(), and EXTRACT() for more fine-grained control.

Modification

To modify dates, there are DATE_ADD() and DATE_SUB() for the addition and subtraction of dates, respectively. Likewise, we can modify time values using ADDTIME() and SUBTIME(). For combining both data types, MySQL provides us with TIMESTAMP() and TIMESTAMPADD(), which lack respective counterparts for subtraction. However, both functions also accept negative values that enable subtraction implicitly.

Comparison

Creating, accessing, and modifying temporal values in MySQL covers many of the functions provided for date and time. However, the comparison remains a fourth use case for working with temporal values. For dates, DATE_DIFF() enables comparison of two dates and yields the difference between them in days. Similarly, MySQL allows us to compare times with TIMEDIFF(), yielding the difference between two-time values as another time value. For combinations of date and time, TIMESTAMPDIFF() provides great flexibility for comparing these values, where we can decide on the unit of the return value.