📖 MySQL Date and Time
Using date and time in MySQL is different than strings and numbers. The complexity of dates and times can challenge developers. Here's a look at some of the more common uses.
- DATE
- Entry and display is in this format "yyyy-mm-dd"
- TIME
- Entry and display is in this format "hh:mm:ss"
- DATETIME
- "yyyy-mm-dd hh:mm:ss"
- TIMESTAMP
- Handy for automatically placing the current system date and time into a field but will only store values through the year 2037.
- YEAR(2) or YEAR(4)
- 2 digit or 4 digit year values.
Here is a list of all the date and time functions in MySQL. There are more here than what is in the book. Just wanted you to know the possibilities.
| Name | Description |
|---|---|
| ADDDATE() | Add time values (intervals) to a date value |
| ADDTIME() | Add time |
| CONVERT_TZ() | Convert from one timezone to another |
| CURDATE() | Return the current date |
| CURRENT_DATE(), CURRENT_DATE | Synonyms for CURDATE() |
| CURRENT_TIME(), CURRENT_TIME | Synonyms for CURTIME() |
| CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP | Synonyms for NOW() |
| CURTIME() | Return the current time |
| DATE_ADD() | Add time values (intervals) to a date value |
| DATE_FORMAT() | Format date as specified |
| DATE_SUB() | Subtract a time value (interval) from a date |
| DATE() | Extract the date part of a date or datetime expression |
| DATEDIFF() | Subtract two dates |
| DAY() | Synonym for DAYOFMONTH() |
| DAYNAME() | Return the name of the weekday |
| DAYOFMONTH() | Return the day of the month (0-31) |
| DAYOFWEEK() | Return the weekday index of the argument |
| DAYOFYEAR() | Return the day of the year (1-366) |
| EXTRACT() | Extract part of a date |
| FROM_DAYS() | Convert a day number to a date |
| FROM_UNIXTIME() | Format UNIX timestamp as a date |
| GET_FORMAT() | Return a date format string |
| HOUR() | Extract the hour |
| LAST_DAY | Return the last day of the month for the argument |
| LOCALTIME(), LOCALTIME | Synonym for NOW() |
| LOCALTIMESTAMP, LOCALTIMESTAMP() | Synonym for NOW() |
| MAKEDATE() | Create a date from the year and day of year |
| MAKETIME() | Create time from hour, minute, second |
| MICROSECOND() | Return the microseconds from argument |
| MINUTE() | Return the minute from the argument |
| MONTH() | Return the month from the date passed |
| MONTHNAME() | Return the name of the month |
| NOW() | Return the current date and time |
| PERIOD_ADD() | Add a period to a year-month |
| PERIOD_DIFF() | Return the number of months between periods |
| QUARTER() | Return the quarter from a date argument |
| SEC_TO_TIME() | Converts seconds to 'HH:MM:SS' format |
| SECOND() | Return the second (0-59) |
| STR_TO_DATE() | Convert a string to a date |
| SUBDATE() | Synonym for DATE_SUB() when invoked with three arguments |
| SUBTIME() | Subtract times |
| SYSDATE() | Return the time at which the function executes |
| TIME_FORMAT() | Format as time |
| TIME_TO_SEC() | Return the argument converted to seconds |
| TIME() | Extract the time portion of the expression passed |
| TIMEDIFF() | Subtract time |
| TIMESTAMP() | With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments |
| TIMESTAMPADD() | Add an interval to a datetime expression |
| TIMESTAMPDIFF() | Subtract an interval from a datetime expression |
| TO_DAYS() | Return the date argument converted to days |
| UNIX_TIMESTAMP() | Return a UNIX timestamp |
| UTC_DATE() | Return the current UTC date |
| UTC_TIME() | Return the current UTC time |
| UTC_TIMESTAMP() | Return the current UTC date and time |
| WEEK() | Return the week number |
| WEEKDAY() | Return the weekday index |
| WEEKOFYEAR() | Return the calendar week of the date (0-53) |
| YEAR() | Return the year |
| YEARWEEK() | Return the year and week |
Format
SELECT CURDATE();
-- returns the current date in the form 'YYYY-MM-DD'Many of the functions require a date parameter for processing. The date passed to the function can be a literal date '2021-04-15' or a variable date 'CURDATE()'.
Example
SELECT MONTHNAME('2021-04-15');
-- returns the month name from the date passed ex: 'April'
SELECT MONTHNAME(CURDATE());
-- returns the month name for the current dateDATE_FORMAT()
The DATE_FORMAT() function is used to display date/time data in different formats.
Format
SELECT DATE_FORMAT(date, format);
-- Where date is a valid date and format specifies the output format for the date/time.The date passed to the function can be a literal date '2021-04-15' or a variable date 'CURDATE()'. Format codes and date literals must be in quotes. You can include additional text in the format.
Example
SELECT DATE_FORMAT('2021-04-15', '%a, %M %e, %Y');
-- returns the date like this 'Thu, April 15, 2021'
SELECT DATE_FORMAT(CURDATE(), 'Today is %a, %M %e, %Y');
-- returns the formatted date for the current date with the text 'Today is'| Format | Description |
|---|---|
| %a | Abbreviated weekday name |
| %b | Abbreviated month name |
| %c | Month, numeric |
| %D | Day of month with English suffix |
| %d | Day of month, numeric (00-31) |
| %e | Day of month, numeric (0-31) |
| %f | Microseconds |
| %H | Hour (00-23) |
| %h | Hour (01-12) |
| %I | Hour (01-12) |
| %i | Minutes, numeric (00-59) |
| %j | Day of year (001-366) |
| %k | Hour (0-23) |
| %l | Hour (1-12) |
| %M | Month name |
| %m | Month, numeric (00-12) |
| %p | AM or PM |
| %r | Time, 12-hour (hh:mm:ss AM or PM) |
| %S | Seconds (00-59) |
| %s | Seconds (00-59) |
| %T | Time, 24-hour (hh:mm:ss) |
| %U | Week (00-53) where Sunday is the first day of week |
| %u | Week (00-53) where Monday is the first day of week |
| %V | Week (01-53) where Sunday is the first day of week, used with %X |
| %v | Week (01-53) where Monday is the first day of week, used with %x |
| %W | Weekday name |
| %w | Day of the week (0=Sunday, 6=Saturday) |
| %X | Year of the week where Sunday is the first day of week, four digits, used with %V |
| %x | Year of the week where Monday is the first day of week, four digits, used with %v |
| %Y | Year, four digits |
| %y | Year, two digits |
Searching for Dates and Times
Because MySQL DATETIME fields have both a date and a time, it can present a problem when searching for a specific date or time since the field will include both a date and a time. To overcome this issue, use the following methods.
Search for Date
- Method 1
- Use a range of dates (most efficient).
WHERE start_date >= '2014-10-22' AND start_date < '2014-10-23';- Method 2
- Use functions to specify month, day and year.
WHERE MONTH(start_date) = 10 AND DAY(start_date)=22 AND YEAR(start_date)= 2014;- Method 3
- Search for a formatted date.
WHERE DATE_FORMAT(start_date, '%m-%d-%Y') = '10-22-2014';Search for Time
- Method 1
- Search for a formatted time.
WHERE DATE_FORMAT(start_date, '%t') = '10:00:00';- Method 2
- Use functions to specify time.
WHERE EXTRACT(HOUR_SECOND FROM start_date) = 100000;