в 13:44, 26 мая 2010

2010-05-26T13:44:56Z

Admin: 1 версия

2010-05-26T09:50:41Z

1 версия

Новая страница

== Changing Date Values==

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



<source lang="sql">
DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)</source>

== Current Date and Time==

There are several methods for obtaining the current date and time:
<OL><LI>NOW()/SYSDATE()/CURRENT_TIMESTAMP</LI><LI>CURDATE()/CURRENT_DATE</LI><LI>CURTIME()/CURRENT_TIME</LI></OL>



<source lang="sql">
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></source>

== 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).



<source lang="sql">
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></source>

== Extracting Years, Quarters, Months, and Weeks==

MySQL has a range of function for extracting numerical data from a date:
<OL><LI>YEAR(date)</LI><LI>QUARTER(date)</LI><LI>MONTH(date)</LI><LI>WEEK(date[,firstday])</LI><LI>YEARWEEK(date[,firstday])</LI></OL>
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



<source lang="sql">
mysql>
mysql> SELECT DAYNAME("1989-07-20");
+-----------------------+
| DAYNAME("1989-07-20") |
+-----------------------+
| Thursday |
+-----------------------+
1 row in set (0.00 sec)
mysql></source>

== 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.



<source lang="sql">
mysql>
mysql> SELECT DAYOFYEAR(20000201);
+---------------------+
| DAYOFYEAR(20000201) |
+---------------------+
| 32 |
+---------------------+
1 row in set (0.01 sec)</source>

== 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



<source lang="sql">
mysql>
mysql> SELECT MONTHNAME("0-2-1");
+--------------------+
| MONTHNAME("0-2-1") |
+--------------------+
| February |
+--------------------+
1 row in set (0.00 sec)
mysql></source>

== 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.



<source lang="sql">
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></source>

← Предыдущая	Версия 13:44, 26 мая 2010
(нет различий)

MySQL Tutorial/Date Time Functions/Introduction - История изменений

в 13:44, 26 мая 2010

Admin: 1 версия