PHP date query function practical code-PHP Tutorial-php.cn

date_default_timezone_set('PRC'); //Default time zone
echo "Today:",date("Y-m-d",time()),"
" ;
echo "Today:",date("Y-m-d",strtotime("18 june 2008")),"
";
echo "Yesterday:",date("Y-m-d",strtotime("-1 day ")), "
";
echo "Tomorrow:",date("Y-m-d",strtotime("+1 day")), "
";
echo "One week later:",date ("Y-m-d",strtotime("+1 week")), "
";
echo "One week, two days, four hours and two seconds later:",date("Y-m-d G:H:s",strtotime( "+1 week 2 days 4 hours 2 seconds")), "
";
echo "Next Thursday:",date("Y-m-d",strtotime("next Thursday")), "
";
echo "Last Monday:".date("Y-m-d",strtotime("last Monday"))."
";
echo "One month ago:".date("Y-m-d",strtotime( "last month"))."
";
echo "One month later:".date("Y-m-d",strtotime("+1 month"))."
";
echo "十After the year: ".date("Y-m-d",strtotime("+10 year"))."
";
$a = date("w",time()); //Get today is this week Day of the week
$startweekdate =date("Y-m-d H:i:s",strtotime($today)-($a-1)*24*3600); //Get Monday of this week
$endweekdate =date("Y-m-d H :i:s",strtotime($today)+(7-$a+1)*24*3600); //Get the last day of this week and the first day of next week
$endnextweekdate=date("Y-m-d" ,strtotime($endweekdate)+7*24*3600);//The last day of next week
?>
$d = time(); //Pending date
$w = date("w",$d); //What day of the week is this day?
$d0 = date("Y-m-d",strtotime("-$w day",$d)); / /Start of week
$d6 = date("Y-m-d",strtotime((6-$w)." day",$d)); //End of week
?>
$d = time( ); //Pending date
$w = date("w",$d); //What day of the week is this day
$d0 = mktime (0,0,0, date("m"),date("d") - $w,date("Y"));//Start of week
$d0 = mktime (0,0,0,date("m"),date( "d") - $w + 6,date("Y"));//End of week
?>
$date = time();
$w = date("w",$d);
$d0 = date("Y-m-d",strtotime("-$w day",$date)); //Start of week
$d6 = date("Y-m-d",strtotime ((6-$w)." day",$date)); //End of week
?>
$date1 = date("Y-m-d",strtotime("Sunday")); //Start of week
$date2 = date("Y-m-d",strtotime("Saturday")); //End of week
$date1 = date("Y-m-d",strtotime("last Sunday")); //Start of last week
$date2 = date(" Y-m-d",strtotime("last Saturday")); //End of last week
//Get the year and month of the system
$tmp_date=date("Ym");
//Cut out the year
$tmp_year=substr($tmp_date ,0,4);
//Cut out the month
$tmp_mon =substr($tmp_date,4,2);
$tmp_nextmnoth=mktime(0,0,0,$tmp_mon+1,1,$tmp_year);
$tmp_forwardmnoth=mktime(0,0,0,$tmp_mon-1,1,$tmp_year);
//Get the next month of the current month
$fm_next_mnoth=date("Ym",$tmp_nextmonth);
//Get The previous month of the current month
$fm_forward_mnoth=date("Ym",$tmp_forwardmonth);
?>
$d=array("日","一","二","三", "Four","Friday","Saturday");
$whatday="week".$d[date("w",strtotime($today))]; //Get today's day of the week
$time = abs( (strtotime("2008-12-25") - strtotime(date("Y-m-d")))/86400);/Get the number of days difference between two dates
?>

Copy the code

See 7.3.6 Date and Time Types for a description of the range of values each type has and the valid formats for specifying date and time values.

Here is an example using date functions. Select all records whose date_col value is within the last 30 days:

mysql> SELECT something FROM table
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

Copy code

DAYOFWEEK(date) Returns the day of the week index of date (1=Sunday, 2=Monday, ...7=Saturday). These index values correspond to the ODBC standard.

mysql> select DAYOFWEEK('1998-02-03');
-> 3

Copy code

WEEKDAY(date) Returns the day of the week index of date (0=Monday, 1=Tuesday, ...6=Sunday).

mysql> select WEEKDAY('1997-10-04 22:23:00');
-> 5
mysql> select WEEKDAY('1997-11-05');
-> 2

Copy code

DAYOFMONTH(date) Returns the day of the month for date, in the range 1 to 31.

mysql> select DAYOFMONTH('1998-02-03');
-> 3

Copy code

DAYOFYEAR(date) Returns the day of the year for date, in the range 1 to 366.

mysql> select DAYOFYEAR('1998-02-03');
-> 34

Copy code

MONTH(date) Returns the month of date, ranging from 1 to 12.

mysql> select MONTH('1998-02-03');
-> 2

Copy code

DAYNAME(date) Returns the day of the week name for date.

mysql> select DAYNAME("1998-02-05");
-> 'Thursday'

Copy code

MONTHNAME(date) Returns the month name of date.

mysql> select MONTHNAME("1998-02-05");
-> 'February'

Copy code

QUARTER(date) Returns the quarter of the year for date, ranging from 1 to 4.

mysql> select QUARTER('98-04-01');
-> 2

Copy code

WEEK(date) WEEK(date,first) For places where Sunday is the first day of the week, there is a single argument that returns the week number of the date, in the range 0 to 52. The 2 argument form WEEK() allows You specify whether the week starts on Sunday or Monday. If the second parameter is 0, the week starts on Sunday, if the second parameter is 1, Starting on Monday. &Mysql & GT; Select Week ('1998-02-20');

& GT; 7

Mysql & GT; Select week ('1998-02-20', 0); & gt; 7

Mysql & GT; select WEEK('1998-02-20',1);

Copy code
YEAR(date) Returns the year of date, ranging from 1000 to 9999.

mysql> select YEAR('98-02-03');

-> 1998

Copy code

HOUR(time) Returns the hour of time, ranging from 0 to 23.

mysql> select HOUR('10:05:03');

-> 10

Copy code

MINUTE(time) Returns the minute of time, ranging from 0 to 59.

mysql> select MINUTE('98-02-03 10:05:03');

-> 5

Copy code

SECOND(time) Returns the number of seconds of time, ranging from 0 to 59.

mysql> select SECOND('10:05:03');

-> 3

Copy code

PERIOD_ADD(P,N) Add N months to phase P (in format YYMM or YYYYMM). Returns the value in the format YYYYMM. Note that the phase parameter P is not a date value.

mysql> select PERIOD_ADD(9801,2);

-> 199803

Copy code

PERIOD_DIFF(P1,P2) Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period parameters P1 and P2 are not date values.

mysql> select PERIOD_DIFF(9802,199703);

-> 11

Copy code

DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type) ADDDATE(date,INTERVAL expr type) SUBDATE(date,INTERVAL expr type) These functions perform date operations. As of MySQL 3.22, they are new. ADDDATE() and SUBDATE() are synonyms of DATE_ADD() and DATE_SUB(). In MySQL 3.23, you can use + and - instead of DATE_ADD() and DATE_SUB(). (see example) date is a date specifying the start date DATETIME or DATE value, expr is an expression specifying the interval value added to or subtracted from the start date, expr is a string; it can be A "-" start indicates a negative interval. type is a keyword that specifies how the expression should be interpreted. EXTRACT(type FROM date) function from date Returns the "type" interval. The following table shows how the type and expr parameters are related: type value meaning expected expr format SECOND seconds SECONDS MINUTE minutes MINUTES HOUR time HOURS DAY DAYS MONTH Month MONTHS YEAR YEARS MINUTE_SECOND minutes and seconds "MINUTES:SECONDS" HOUR_MINUTE hours and minutes "HOURS:MINUTES" DAY_HOUR days and hours "DAYS HOURS" YEAR_MONTH Year and month "YEARS-MONTHS" HOUR_SECOND hours, minutes, "HOURS:MINUTES:SECONDS" DAY_MINUTE days, hours, minutes "DAYS HOURS:MINUTES" DAY_SECOND days, hours, minutes, seconds "DAYS HOURS:MINUTES:SECONDS"

MySQL allows any punctuation delimiter in expr format. Indicates that recommended delimiters are displayed. If the date parameter is a DATE value and your calculation is just Contains the YEAR, MONTH, and DAY components (i.e., no time component), and the result is a DATE value. Otherwise the result is a DATETIME value.
2. mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
3. -> 1998-01-01 00:00:00
4. mysql> 997 -12-31";
5. -> 1998-01-01
6. mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;
7. -> 1997-12-31 23:59:59
8. mysql> SELECT DATE_ADD ("1997-12-31 23:59:59",
9. INTERVAL 1 SECOND);
10. -> 1998-01-01 00:00:00
11. mysql> SELECT DATE_ADD("1997-12-31 23:59: 59",
12. INTERVAL 1 DAY);
13. -> 1998-01-01 23:59:59
14. mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
15. INTERVAL "1:1" MINUTE_SECOND );
16. -> 1998-01-01 00:01:00
17. mysql> SELECT DATE_SUB("1998-01-01 00:00:00",
18. INTERVAL "1 1:1:1" DAY_SECOND);
19. - > 1997-12-30 22:58:59
20. mysql> SELECT DATE_ADD("1998-01-01 00:00:00",
21. INTERVAL "-1 10" DAY_HOUR);
22. -> 1997-12-30 14:00:00
23. mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
24. -> 1997-12-02
25. mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
26. -> 1999
27. mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
28. -> 199907
29. mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:0 2: 03");
30. -> 20102
Copy code
If you specify an interval value that is too short (excluding the interval expected by the type keyword), MySQL assumes that you omitted the leftmost part of the interval value. For example, If you specify a type of DAY_SECOND, the value expr is expected to have day, hour, minute, and second components. If you specify a value like "1:10", MySQL assumes that the day and hour parts are missing and the values represent minutes and seconds. In other words, "1:10" DAY_SECOND is equivalent to "1:10" MINUTE_SECOND interpretation, which is ambiguous with the way MySQL interprets the TIME value to represent an elapsed time rather than as a time of day. If you use a date that is indeed incorrect, The result is NULL. If you increment MONTH, YEAR_MONTH, or YEAR and the resulting date is greater than the maximum number of days in the new month, the days are adjusted to the maximum number of days in the new month.
2. mysql> select DATE_ADD('1998-01-30', Interval 1 month);
3. -> 1998-02-28
Copy code
Note that from the previous example the word INTERVAL and type keywords are not case-sensitive. TO_DAYS(date) Given a date, return a number of days (from year 0).
2. mysql> select TO_DAYS(950501);
3. -> 728779
4. mysql> select TO_DAYS('1997-10-07');
5. -> 729669
Copy code
TO_DAYS() Not intended for use with values before the advent of the Gregorian calendar (1582).

FROM_DAYS(N) Given a number of days N, return a DATE value.
2. mysql> select FROM_DAYS(729669);
3. -> '1997-10-07'
Copy code
TO_DAYS() is not intended to be used with values before the advent of the Gregorian calendar (1582).

DATE_FORMAT(date,format) Format the date value according to the format string. The following modifiers can be used in the format string: %M month name (January......December) %W day of the week name (Sunday......Saturday) %D The day of the month with an English prefix (1st, 2nd, 3rd, etc.) %Y year, number, 4 digits %y year, number, 2 digits %a abbreviated day of the week name (Sun......Sat) %d The number of days in the month, number (00……31) %e The number of days in the month, number (0……31) %m month, number (01……12) %c month, number (1……12) %b abbreviated month name (Jan......Dec) %j Number of days in a year (001……366) %H hours (00……23) %k hours (0...23) %h hours (01……12) %I hours (01……12) %l hours (1...12) %i minutes, number (00……59) %r time, 12 hours (hh:mm:ss [AP]M) %T time, 24 hours (hh:mm:ss) %S seconds (00……59) %s seconds (00……59) %p AM or PM %w The number of days in a week (0=Sunday...6=Saturday) %U day of the week (0...52), where Sunday is the first day of the week %u day of the week (0...52), where Monday is the first day of the week %% A literal "%".

All other characters are copied into the result without interpretation.
2. mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
3. -> 'Saturday October 1997'
4. mysql> select DATE_FORMAT(' 1997-10-04 22:23:00', '%H:%i:%s');
5. -> '22:23:00'
6. mysql> select DATE_FORMAT('1997-10-04 22:23 :00',
7. '%D %y %a %d %m %b %j');
8. -> '4th 97 Sat 04 10 Oct 277'
9. mysql> select DATE_FORMAT('1997-10-04 22: 23:00',
10. '%H %k %I %r %T %S %w');
11. -> '22 22 10 10:23:00 PM 22:23:00 00 6'
Copy the code
In MySQL3.23, % is required before the format modifier character. In earlier versions of MySQL, % was optional.

TIME_FORMAT(time,format) This is used like the DATE_FORMAT() function above, but the format string can only contain those format modifiers that handle hours, minutes, and seconds. Other modifiers produce a NULL value or 0. CURDATE() CURRENT_DATE Returns today's date value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
2. mysql> select CURDATE();
3. -> '1997-12-15'
4. mysql> select CURDATE() + 0;
5. -> 19971215
Copy code
CURTIME() CURRENT_TIME Returns the current time value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context.
2. mysql> select CURTIME();
3. -> '23:50:26'
4. mysql> select CURTIME() + 0;
5. -> 235026
Copy code
NOW() SYSDATE() CURRENT_TIMESTAMP Returns the current date and time in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function operates on a string or a number context is used.
2. mysql> select NOW();
3. -> '1997-12-15 23:50:26'
4. mysql> select NOW() + 0;
5. -> 19971215235026
Copy code
UNIX_TIMESTAMP() UNIX_TIMESTAMP(date) If called without arguments, returns a Unix timestamp (number of seconds since '1970-01-01 00:00:00' GMT). If UNIX_TIMESTAMP() uses a A date parameter is called, which returns the number of seconds since '1970-01-01 00:00:00' GMT. date can be a DATE string or a DATETIME A string, a TIMESTAMP, or a number of local time in YYMMDD or YYYYMMDD format. mysql> select UNIX_TIMESTAMP();
2. -> 882226357
3. mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
4. -> 875996580
Copy code
When UNIX_TIMESTAMP is used for a TIMESTAMP column, the function will accept the value directly, without the implicit "string-to-unix-timestamp" transformation.

FROM_UNIXTIME(unix_timestamp) Returns the value represented by the unix_timestamp parameter in the format 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS, depending on whether the function is in a string or is used in a numerical context.
2. mysql> select FROM_UNIXTIME(875996580);
3. -> '1997-10-04 22:23:00'
4. mysql> select FROM_UNIXTIME(875996580) + 0; -> 19971004222300
Copy Code
FROM_UNIXTIME(unix_timestamp,format) Returns a string representing a Unix timestamp, formatted according to the format string. format can contain the items listed with the DATE_FORMAT() function the same modifiers.
2. mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(),
3. '%Y %D %M %h:%i:%s %x');
4. -> '1997 23rd December 03:43:30 x '
Copy code
SEC_TO_TIME(seconds) Returns the seconds parameter, converted to hours, minutes, and seconds. The value is formatted in 'HH:MM:SS' or HHMMSS, depending on whether the function is in a string or a number. used in context.
2. mysql> select SEC_TO_TIME(2378);
3. -> '00:39:38'
4. mysql> select SEC_TO_TIME(2378) + 0;
5. -> 3938
Copy code
TIME_TO_SEC (time) Return the time parameter, converted to seconds.
2. mysql> select TIME_TO_SEC('22:23:00');
3. -> 80580
4. mysql> select TIME_TO_SEC('00:39:38');
5. -> 2378
Copy code