MySQL Tutorial/Date Time Functions/Introduction

Changing Date Values

DATE_ADD() and DATE_SUB() add and subtract time to a date.

Syntax:

DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)

Current Date and Time

There are several methods for obtaining the current date and time:

1. NOW()/SYSDATE()/CURRENT_TIMESTAMP
2. CURDATE()/CURRENT_DATE
3. CURTIME()/CURRENT_TIME

mysql>
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2007-07-23 18:58:25 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT NOW()+0;
+-----------------------+
| NOW()+0               |
+-----------------------+
| 20070723185825.000000 |
+-----------------------+
1 row in set (0.00 sec)
mysql>

Date and Time Functions

Name Description ADDDATE() Add dates 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 two dates DATE_FORMAT() Format date as specified DATE_SUB() Subtract two dates 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 (1-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 date as a UNIX timestamp 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 returns a time value calculated from the hour, minute, and second arguments 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() When invoked with three arguments a synonym for DATE_SUB() 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 (1-53) YEAR() Return the year YEARWEEK() Return the year and week

Quote: dev.mysql.ru/doc/refman/5.1/en/index.html

Days Since 1 A.D.

TO_DAYS(date) returns the number of days since the first day in the year 1 A.D. (there was no year 0).

The opposite effect is obtained by using FROM_DAYS(number).

mysql>
mysql> select TO_DAYS("2000-01-01") ;
+-----------------------+
| TO_DAYS("2000-01-01") |
+-----------------------+
|                730485 |
+-----------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> SELECT FROM_DAYS(1000000);
+--------------------+
| FROM_DAYS(1000000) |
+--------------------+
| 2737-11-28         |
+--------------------+
1 row in set (0.02 sec)
mysql>

Extracting Years, Quarters, Months, and Weeks

MySQL has a range of function for extracting numerical data from a date:

1. YEAR(date)
2. QUARTER(date)
3. MONTH(date)
4. WEEK(date[,firstday])
5. YEARWEEK(date[,firstday])

14. 1. Introduction 14. 1. 1. <A href="/Tutorial/MySQL/0280__Date-Time-Functions/DateandTimeFunctions.htm">Date and Time Functions</a> 14. 1. 2. <A href="/Tutorial/MySQL/0280__Date-Time-Functions/ExtractionFunctions.htm">Extraction Functions</a> 14. 1. 3. <A href="/Tutorial/MySQL/0280__Date-Time-Functions/ChangingDateValues.htm">Changing Date Values</a> 14. 1. 4. <A href="/Tutorial/MySQL/0280__Date-Time-Functions/GettingDayInformation.htm">Getting Day Information</a> 14. 1. 5. <A href="/Tutorial/MySQL/0280__Date-Time-Functions/GettingNamesforMonthsandDays.htm">Getting Names for Months and Days</a> 14. 1. 6. Extracting Years, Quarters, Months, and Weeks 14. 1. 7. <A href="/Tutorial/MySQL/0280__Date-Time-Functions/CurrentDateandTime.htm">Current Date and Time</a> 14. 1. 8. <A href="/Tutorial/MySQL/0280__Date-Time-Functions/DaysSince1AD.htm">Days Since 1 A.D.</a> 14. 1. 9. <A href="/Tutorial/MySQL/0280__Date-Time-Functions/SecondsSincetheBeginningoftheDay.htm">Seconds Since the Beginning of the Day</a>

Extraction Functions

Extraction functions extract specific information about a date (year, month, day etc).

These include:

Function Displays Example DAYOFMONTH(date) The numeric day of the month 01, 10, 17, 24 etc DAYNAME(date) The Name of the day Monday, Wednesday, Friday etc MONTH(date) The numeric month 01, 04, 08, 11 etc MONTHNAME(date) The Month name January, April, August etc YEAR(date) Four digit year 1998, 2000, 2002, 2003 etc HOUR(time) Hour (24 hour clock) 07, 11, 16, 23 etc MINUTE(time) Minutes 01, 16, 36, 49 etc SECOND(time) Seconds 01, 16, 36, 49 etc DAYOFYEAR(date) Numeric day of the year 1, 366

mysql>
mysql> SELECT DAYNAME("1989-07-20");
+-----------------------+
| DAYNAME("1989-07-20") |
+-----------------------+
| Thursday              |
+-----------------------+
1 row in set (0.00 sec)
mysql>

Getting Day Information

There are four functions for converting a date to a day number:

DAYOFYEAR(date)

DAYOFMONTH(date)

DAYOFWEEK(date)

WEEKDAY(date)

DAYOFYEAR(date) returns the day of the year for a given date (in numeric format).

DAYOFYEAR(date) returns how many days since January 1 of that year.

mysql>
mysql> SELECT DAYOFYEAR(20000201);
+---------------------+
| DAYOFYEAR(20000201) |
+---------------------+
|                  32 |
+---------------------+
1 row in set (0.01 sec)

Getting Names for Months and Days

There are two functions that can be used to look up month and day names for a given date:

MONTHNAME(date)

DAYNAME(date)

MONTHNAME(date) can be used to obtain the name of the month in which date occurs as a string

mysql>
mysql> SELECT MONTHNAME("0-2-1");
+--------------------+
| MONTHNAME("0-2-1") |
+--------------------+
| February           |
+--------------------+
1 row in set (0.00 sec)
mysql>

Seconds Since the Beginning of the Day

TIME_TO_SEC(time) returns the time in seconds (the number of seconds since the beginning of that day).

SEC_TO_TIME(seconds) does the opposite, converting the number of seconds from the beginning of the day into HH:MM:SS or HHMMSS format.

mysql>
mysql> select TIME_TO_SEC("01.00.00");
+-------------------------+
| TIME_TO_SEC("01.00.00") |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql> SELECT TIME_TO_SEC(110);
+------------------+
| TIME_TO_SEC(110) |
+------------------+
|               70 |
+------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT SEC_TO_TIME(70);
+-----------------+
| SEC_TO_TIME(70) |
+-----------------+
| 00:01:10        |
+-----------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT SEC_TO_TIME(1000)+0;
+---------------------+
| SEC_TO_TIME(1000)+0 |
+---------------------+
|         1640.000000 |
+---------------------+
1 row in set (0.02 sec)
mysql>