Date and Time Functions

14 February 2012 By Nithya Vasudevan 518 views No Comment
0 Flares Twitter 0 Facebook 0 Google+ 0 0 Flares ×

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.

SpecifierDescription
%aAbbreviated weekday name (Sun..Sat)
%bAbbreviated month name (Jan..Dec)
%cMonth, numeric (0..12)
%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%dDay of the month, numeric (00..31)
%eDay of the month, numeric (0..31)
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%IHour (01..12)
%iMinutes, numeric (00..59)
%jDay of year (001..366)
%kHour (0..23)
%lHour (1..12)
%MMonth name (January..December)
%mMonth, numeric (00..12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00..53), where Sunday is the first day of the week
%uWeek (00..53), where Monday is the first day of the week
%VWeek (01..53), where Sunday is the first day of the week; used with %X
%vWeek (01..53), where Monday is the first day of the week; used with %x
%WWeekday name (Sunday..Saturday)
%wDay of the week (0=Sunday..6=Saturday)
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, numeric (two digits)
%%A literal “%” character
%xx, 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 ValueExpected expr Format
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
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');

Tags: , , , , , , , , , , , , , , , , ,