Date and Time Functions
CURDATE(),CURRENT_DATE, CURRENT_DATE()
Returns the current date as a value in ‘YYYY-MM-DD’ or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
SELECT CURDATE();
CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().
CURTIME(), CURRENT_TIME, CURRENT_TIME()
Returns the current time as a value in ‘HH:MM:SS’ or HHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.
SELECT CURTIME();
CURRENT_TIME and CURRENT_TIME() are synonyms for CURTIME().
NOW()
Returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.
SELECT NOW();
NOW() returns a constant time that indicates the time at which the statement began to execute. Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.
CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().
SYSDATE()
Returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.
SYSDATE() returns the time at which it executes. This differs from the behavior for NOW(), which returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.)
SELECT SYSDATE();
DATE_FORMAT(date,format)
Formats the date value according to the format string.
The following specifiers may be used in the format string. The “%” character is required before format specifier characters.
| Specifier | Description |
|---|---|
| %a | Abbreviated weekday name (Sun..Sat) |
| %b | Abbreviated month name (Jan..Dec) |
| %c | Month, numeric (0..12) |
| %D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
| %d | Day of the month, numeric (00..31) |
| %e | Day of the month, numeric (0..31) |
| %f | Microseconds (000000..999999) |
| %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 (January..December) |
| %m | Month, numeric (00..12) |
| %p | AM or PM |
| %r | Time, 12-hour (hh:mm:ss followed by 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 the week |
| %u | Week (00..53), where Monday is the first day of the week |
| %V | Week (01..53), where Sunday is the first day of the week; used with %X |
| %v | Week (01..53), where Monday is the first day of the week; used with %x |
| %W | Weekday name (Sunday..Saturday) |
| %w | Day of the week (0=Sunday..6=Saturday) |
| %X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
| %x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
| %Y | Year, numeric, four digits |
| %y | Year, numeric (two digits) |
| %% | A literal “%” character |
| %x | x, for any “x” not listed above |
SELECT DATE_FORMAT(NOW(), '%D %b %Y');
STR_TO_DATE(str,format) – String to Date
This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.
DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)
These functions perform date arithmetic.
- The ‘date’ argument specifies the starting date or datetime value.
- ‘expr’ is an expression specifying the interval value to be added or subtracted from the starting date.
- ‘expr’ is a string; it may start with a “-” for negative intervals.
- ‘unit’ is a keyword indicating the units in which the expression should be interpreted.
The ‘INTERVAL’ keyword and the ‘unit’ specifier are not case sensitive.
The following table shows the expected form of the ‘expr’ argument for each unit value.
| unit Value | Expected expr Format |
|---|---|
| MICROSECOND | MICROSECONDS |
| SECOND | SECONDS |
| MINUTE | MINUTES |
| HOUR | HOURS |
| DAY | DAYS |
| WEEK | WEEKS |
| MONTH | MONTHS |
| QUARTER | QUARTERS |
| YEAR | YEARS |
| SECOND_MICROSECOND | ‘SECONDS.MICROSECONDS’ |
| MINUTE_MICROSECOND | ‘MINUTES:SECONDS.MICROSECONDS’ |
| MINUTE_SECOND | ‘MINUTES:SECONDS’ |
| HOUR_MICROSECOND | ‘HOURS:MINUTES:SECONDS.MICROSECONDS’ |
| HOUR_SECOND | ‘HOURS:MINUTES:SECONDS’ |
| HOUR_MINUTE | ‘HOURS:MINUTES’ |
| DAY_MICROSECOND | ‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’ |
| DAY_SECOND | ‘DAYS HOURS:MINUTES:SECONDS’ |
| DAY_MINUTE | ‘DAYS HOURS:MINUTES’ |
| DAY_HOUR | ‘DAYS HOURS’ |
| YEAR_MONTH | ‘YEARS-MONTHS’ |
Example: In a ‘lending’ table of ‘Library’ Database, the ‘due_date’ should be 5 days from the ‘issue_date’. There we can use the DATE_ADD() function as follows.
INSERT INTO lending(book_id, user_id, issue_date, due_date) VALUES(1025, 536, NOW(), DATE_ADD(issue_date, INTERVAL 5 DAY));
DATEDIFF(expr1,expr2)
- DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other.
- expr1 and expr2 are date or date-and-time expressions.
- Only the date parts of the values are used in the calculation.
SELECT DATEDIFF('2011-12-22 23:59:59', '2011-12-20');
SELECT DATEDIFF('2012-01-01', NOW());
TIMEDIFF(expr1,expr2)
Returns expr1 – expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.
SELECT TIMEDIFF('2011-12-22 23:59:59','2011-12-21 11:59:59');
