[ Pobierz całość w formacie PDF ]
.f.qc 12/15/00 15:27 Page 575Appendix I: MySQL Function Reference 575type Meaning Expected expr format valueDAY_MINUTE Days, hours, minutes DAYS HOURS:MINUTES DAY_HOUR Days and hours DAYS HOURS MONTH Months MONTHSYEAR Years YEARSYEAR_MONTH Years and months YEARS-MONTHS MySQL allows any punctuation delimiter in the expr format.The ones shown inthe table are the suggested delimiters.If the date argument is a DATE value andyour calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts),the result is a DATE value.Otherwise, the result is a DATETIME value.mysql> select 2001-01-01 13:00:00 + interval 10 m+--------------------------------------------+| 2001-01-01 13:00:00 + interval 10 minute |+--------------------------------------------+| 2001-01-01 13:10:00 |+--------------------------------------------+1 row in set (0.39 sec)mysql> select 2000-01-01 00:00:00 - interval 1 second;+-------------------------------------------+| 2000-01-01 00:00:00 - interval 1 second |+-------------------------------------------+| 1999-12-31 23:59:59 |+-------------------------------------------+1 row in set (0.00 sec)mysql> select date_add( 2000-01-01 00:00:00 , interval 1:1:1 hour_second);+---------------------------------------------------------------+| date_add( 2000-01-01 00:00:00 , interval 1:1:1 hour_second) |+---------------------------------------------------------------+| 2000-01-01 01:01:01 |+---------------------------------------------------------------+1 row in set (0.00 sec)mysql> select date_sub( 2000-01-01 00:00:00 , interval 1 month);+-----------------------------------------------------+| date_sub( 2000-01-01 00:00:00 , interval 1 month) |+-----------------------------------------------------+ 3537-4 AppI.f.qc 12/15/00 15:27 Page 576576 Part V: Appendixes| 1999-12-01 00:00:00 |+-----------------------------------------------------+1 row in set (0.00 sec)If you specify an interval value that is too short (does not include all the intervalparts that would be expected from the type keyword), MySQL assumes you have leftout the leftmost parts of the interval value.For example, if you specify a type ofDAY_SECOND, the value of expr is expected to have days, hours, minutes, and secondsparts.If you specify a value like 1:10, MySQL assumes that the days and hours partsare missing and that the value represents minutes and seconds.TO_DAYSGiven a date date, this function returns a daynumber (the number of days sinceyear 0).TO_DAYS(date) (used in examples)RETURNS: intTO_DAYS() is not intended for use with values that precede the advent of theGregorian calendar (1582).Note that this is not the same as the PHP mktime() func-tion, which gets the date as of January 1, 1970.See the MySQL UNIX_TIMESTAMPfunction if you need that information.FROM_DAYSGiven a daynumber N, this function returns a DATE value.FROM_DAYS(N) (used in examples)RETURNS: dateFROM_DAYS() is not intended for use with values that precede the advent of theGregorian calendar (1582).DATE_FORMATThis function formats the date value according to the format string.DATE_FORMAT(date,format) (used in examples)RETURNS: stringThe specifiers in Table I-2 may be used in the format string. 3537-4 AppI.f.qc 12/15/00 15:27 Page 577Appendix I: MySQL Function Reference 577TABLE I-2 DATE_FORMAT SPECIFIERSSpecifier Meaning%M Month name (Januarythrough December)%W Weekday name (Sundaythrough Saturday)%D Day of the month with English suffix (1st, 2nd, 3rd, etc.)%Y Year, numeric, four digits%y Year, numeric, two digits%a Abbreviated weekday name (Sun.Sat)%d Day of the month, numeric (00.31)%e Day of the month, numeric (0.31)%m Month, numeric (01.12)%c Month, numeric (1.12)%b Abbreviated month name (Jan.Dec)%j Day of year (001.366)%H Hour (00.23)%k Hour (0.23)%h Hour (01.12)%I Hour (01.12)%l Hour (1.12)%i Minutes, numeric (00.59)%r Time, 12-hour (hh:mm:ss [AP]M)%T Time, 24-hour (hh:mm:ss)%S Seconds (00.59)%s Seconds (00.59)%p AMor PM%w Day of the week (0=Sunday.6=Saturday)%U Week (0.53), where Sunday is the first day of the week%u Week (0.53), where Monday is the first day of the weekContinued 3537-4 AppI.f.qc 12/15/00 15:27 Page 578578 Part V: AppendixesTABLE I-2 DATE_FORMAT SPECIFIERS (Continued)Specifier Meaning%V Week (1.53), where Sunday is the first day of the week; used with %X%v Week (1.53), where Monday is the first day of the week; used with %x%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%% A literal %All other characters are just copied to the result without interpretation.mysql> select date_format( 2001-01-01 , %W %M %d, %Y );+-------------------------------------------+| date_format( 2001-01-01 , %W %M %d, %Y ) |+-------------------------------------------+| Monday January 01, 2001 |+-------------------------------------------+1 row in set (0.00 sec)mysql> select date_format( 2001-01-01 15:30:20 ,-> %W %M %d, %Y %I:%i:%S %p );+----------------------------------------------------------------+| date_format( 2001-01-01 15:30:20 , %W %M %d, %Y %I:%i:%S %p ) |+----------------------------------------------------------------+| Monday January 01, 2001 03:30:20 PM |+----------------------------------------------------------------+1 row in set (0.00 sec)As of MySQL 3.23, the % character is required before format specifier characters.In earlier versions of MySQL, % was optional.TIME_FORMATThis function is used like the DATE_FORMAT() function above, but the format stringmay contain only those format specifiers that handle hours, minutes, and seconds.If specifiers other than hours, minutes, and seconds are included, the function willreturn a NULL value. 3537-4 AppI.f.qc 12/15/00 15:27 Page 579Appendix I: MySQL Function Reference 579TIME_FORMAT(time,format) (used in examples)RETURNS: stringCURDATEThis function returns today s date as a value in YYYY-MM-DD or YYYYMMDD for-mat, depending on whether the function is used in a string or numeric context.CURDATE() or CURRENT_DATE (used in examples)RETURNS: mixedCURTIMEThis function returns the current time as a value in HH:MM:SS or HHMMSS format,depending on whether the function is used in a string or numeric context.CURTIME() or CURRENT_TIMERETURNS: mixedNOWThis function returns the current date and time as a value in YYYY-MM-DDHH:MM:SS or YYYYMMDDHHMMSS format, depending on whether the function isused in a string or numeric context.NOW()orSYSDATE()orCURRENT_TIMESTAMP (used in examples)RETURNS: stringUNIX_TIMESTAMPIf this function is called with no argument, it returns a Unix timestamp (secondssince 1970-01-01 00:00:00 GMT).If UNIX_TIMESTAMP() is called with a date argu-ment, it returns the value of the argument as seconds since 1970-01-01 00:00:00GMT.date may be a DATE string, a DATETIME string, a TIMESTAMP, or a numberin the format YYMMDD or YYYYMMDD in local time.UNIX_TIMESTAMP([date])RETURNS: int 3537-4 AppI.f
[ Pobierz całość w formacie PDF ]