Home > Developer > PHP

PHP date query function code

Source: Internet
Author: User
Tags month name
Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞
PHP date query function code

  3. Date_default_timezone_set ('prc'); // Default time zone

  4. Echo "today:", date ("Y-m-d", time ()),"
    ";
  5. Echo "today:", date ("Y-m-d", strtotime ("18 Jun 2008 ")),"
    ";
  6. Echo "yesterday:", date ("Y-m-d", strtotime ("-1 day ")),"
    ";
  7. Echo "tomorrow:", date ("Y-m-d", strtotime ("+ 1 day ")),"
    ";
  8. Echo "one week later:", date ("Y-m-d", strtotime ("+ 1 week ")),"
    ";
  9. Echo "one week, two days, four hours, two seconds later:", date ("Y-m-d G: H: s ", strtotime ("+ 1 week 2 days 4 hours 2 seconds ")),"
    ";
  10. Echo "next Thursday:", date ("Y-m-d", strtotime ("next Thursday ")),"
    ";
  11. Echo "last Monday:". date ("Y-m-d", strtotime ("last Monday "))."
    ";
  12. Echo "a month ago:". date ("Y-m-d", strtotime ("last month "))."
    ";
  13. Echo "one month later:". date ("Y-m-d", strtotime ("+ 1 month "))."
    ";
  14. Echo "Ten years later:". date ("Y-m-d", strtotime ("+ 10 year "))."
    ";
  15. $ A = date ("w", time (); // Obtain the day of the week today
  16. $ Startweekdate = date ("Y-m-d H: I: s", strtotime ($ today)-($ A-1) * 24*3600); // get this week's Monday
  17. $ Endweekdate = date ("Y-m-d H: I: s", strtotime ($ today) + (7-$ a + 1) * 24*3600 ); // Obtain the last day of the week and the first day of the week
  18. $ Endnextweekdate = date ("Y-m-d", strtotime ($ endweekdate) + 7*24*3600); // The last day of the next week
  19. ?>
  20. $ D = time (); // date to be processed

  21. $ W = date ("w", $ d); // The Day of the week.

  22. $ D0 = date ("Y-m-d", strtotime ("-$ w day", $ d); // week start
  23. $ D6 = date ("Y-m-d", strtotime (6-$ w). "day", $ d); // end of the week
  24. ?>
  25. $ D = time (); // date to be processed
  26. $ W = date ("w", $ d); // The Day of the week.

  27. $ D0 = mktime (, 0, date ("m"), date ("d")-$ w, date ("Y"); // start of a week

  28. $ D0 = mktime (, 0, date ("m"), date ("d")-$ w + 6, date ("Y"); // end of the week
  29. ?>
  30. $ Date = time ();

  31. $ W = date ("w", $ d );

  32. $ D0 = date ("Y-m-d", strtotime ("-$ w day", $ date); // week start
  33. $ D6 = date ("Y-m-d", strtotime (6-$ w). "day", $ date); // end of the week
  34. ?>
  35. $ Date1 = date ("Y-m-d", strtotime ("Sunday"); // start of a week
  36. $ Date2 = date ("Y-m-d", strtotime ("Saturday"); // end of the week
  37. $ Date1 = date ("Y-m-d", strtotime ("last Sunday"); // start last week
  38. $ Date2 = date ("Y-m-d", strtotime ("last Saturday"); // ends last week
  39. // Obtain the system's year and month
  40. $ Tmp_date = date ("Ym ");
  41. // Cut year
  42. $ Tmp_year = substr ($ tmp_date, 0, 4 );
  43. // Cut Out Month
  44. $ Tmp_mon = substr ($ tmp_date, 4,2 );
  45. $ Tmp_nextmnoth = mktime (0, 0, 0, $ tmp_mon + 1, 1, $ tmp_year );
  46. $ Tmp_forwardmnoth = mktime (, 0, $ tmp_mon-1, 1, $ tmp_year );
  47. // Get the next month of the current month
  48. $ Fm_next_mnoth = date ("Ym", $ tmp_nextmonth );
  49. // Obtain the previous month of the current month
  50. $ Fm_forward_mnoth = date ("Ym", $ tmp_forwardmonth );
  51. ?>
  52. $ D = array ("day", "one", "two", "three", "four", "five", "six ");
  53. $ Whatday = "week". $ d [date ("w", strtotime ($ today)]; // Obtain the day of the week
  54. $ Time = abs (strtotime ("")-strtotime (date ("Y-m-d")/86400);/get the number of days difference between two dates
  55. ?>

For the description of the valid format of the value range of each type and the specified date and time value, see 7.3.6 date and time type.

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

  2. Mysql> SELECT something FROM table
  3. WHERE TO_DAYS (NOW ()-TO_DAYS (date_col) <= 30;

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

  2. Mysql> select DAYOFWEEK ('2017-02-03 ');
  3. -> 3

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

  2. Mysql> select WEEKDAY ('2017-10-04 22:23:00 ');
  3. -> 5
  4. Mysql> select WEEKDAY ('2017-11-05 ');
  5. -> 2

DAYOFMONTH (date) returns the date in the month of date, within the range of 1 to 31.

  2. Mysql> select DAYOFMONTH ('2017-02-03 ');
  3. -> 3

DAYOFYEAR (date) returns the number of days in a year from 1 to 366.

  2. Mysql> select DAYOFYEAR ('2017-02-03 ');
  3. -> 34

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

  2. Mysql> select MONTH ('2014-02-03 ');
  3. -> 2

DAYNAME (date) returns the name of the week of date.

  2. Mysql> select DAYNAME ("1998-02-05 ");
  3. -> 'Thursday'

MONTHNAME (date) returns the name of the month of the date.

  2. Mysql> select MONTHNAME ("1998-02-05 ");
  3. -> 'February'

QUARTER (date) returns the QUARTER of a year of date, ranging from 1 to 4.

  2. Mysql> select QUARTER ('98-04-01 ');
  3. -> 2

WEEK (date) WEEK (date, first) for Sunday is the first day of a WEEK, there is a single parameter, return the number of weeks of date, range is 0 to 52. Two parameter formats: WEEK () allows you to specify whether the WEEK starts on Sunday or Monday. If the second parameter is 0, the week starts from Sunday, and if the second parameter is 1, it starts from Monday.

  2. Mysql> select WEEK ('2017-02-20 ');
  3. -> 7
  4. Mysql> select WEEK ('2017-02-20 ', 0 );
  5. -> 7
  6. Mysql> select WEEK ('2017-02-20 ', 1 );
  7. -> 8

YEAR (date) returns the YEAR of date in the range of 1000 to 9999.

  2. Mysql> select YEAR ('98-02-03 ');
  3. -> 1998

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

  2. Mysql> select HOUR ('10: 05: 03 ');
  3. -> 10

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

  2. Mysql> select MINUTE ('98-02-03 10:05:03 ');
  3. -> 5

The number of seconds in which SECOND (time) returns time, ranging from 0 to 59.

  2. Mysql> select SECOND ('10: 05: 03 ');
  3. -> 3

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

  2. Mysql> select PERIOD_ADD (9801,2 );
  3. -> 199803

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

  2. Mysql> select PERIOD_DIFF (9802,199703 );
  3. -> 11

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. For 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 the example) date is a DATETIME or DATE value that specifies the start date. expr is an expression that specifies the interval value added to the start DATE or subtracted from the start date. expr is a string; it can start with a "-" to indicate the negative interval. Type is a keyword that specifies how the expression should be interpreted. The EXTRACT (type FROM date) function returns the "type" interval FROM the date. The following table shows how the type and expr parameters are associated: the value of type indicates the expected expr format: second seconds minute minutes hour time hours day days month year years MINUTE_SECOND MINUTE and SECOND "MINUTES: SECONDS "HOUR_MINUTE hour and minute" HOURS: MINUTES "DAY_HOUR day and hour" days hours "YEAR_MONTH and month" YEARS-MONTHS "HOUR_SECOND hour, minute," HOURS: MINUTES: SECONDS "DAY_MINUTE day, hour, minute" days hours: MINUTES "DAY_SECOND day, hour, minute, second" days hours: MINUTES: SECONDS"

MySQL allows any punctuation separator in the expr format. It indicates that the recommended delimiter is displayed. If the date parameter is a DATE value and your calculation only contains the YEAR, MONTH, and DAY sections (that is, there is no time section), the result is a DATE value. Otherwise, the result is a DATETIME Value.

  2. Mysql> SELECT "23:59:59" + INTERVAL 1 SECOND;
  3. -> 00:00:00
  4. Mysql> select interval 1 DAY + "1997-12-31 ";
  5. -> 1998-01-01
  6. Mysql> SELECT "1998-01-01"-INTERVAL 1 SECOND;
  7. -> 23:59:59
  8. Mysql> SELECT DATE_ADD ("23:59:59 ",
  9. INTERVAL 1 SECOND );
  10. -> 00:00:00
  11. Mysql> SELECT DATE_ADD ("23:59:59 ",
  12. INTERVAL 1 DAY );
  13. -> 23:59:59
  14. Mysql> SELECT DATE_ADD ("23:59:59 ",
  15. INTERVAL "1:1" MINUTE_SECOND );
  16. -> 00:01:00
  17. Mysql> SELECT DATE_SUB ("00:00:00 ",
  18. INTERVAL "1" DAY_SECOND );
  19. -> 1997-12-30 22:58:59
  20. Mysql> SELECT DATE_ADD ("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 "maid ");
  26. -> 1999
  27. Mysql> select extract (YEAR_MONTH FROM "01:02:03 ");
  28. -> 199907
  29. Mysql> select extract (DAY_MINUTE FROM "01:02:03 ");
  30. -> 20102

If you specify an interval value that is too short (excluding the expected interval of the type keyword), MySQL assumes that you have saved the leftmost portion of the interval value. For example, if you specify a type of DAY_SECOND, the value of expr is expected to be one day, hour, minute, and second. If you specify a value like "", MySQL assumes that the days and hours are lost and the value represents minutes and seconds. In other words, "" DAY_SECOND is interpreted as it is equivalent to "" MINUTE_SECOND, this interpretation of MySQL's TIME value indicates that the elapsed TIME is not as a one-day TIME. If you use an incorrect date, the result is NULL. If you increase MONTH, YEAR_MONTH, or YEAR and the result date is greater than the maximum number of days of the new MONTH, the day is adjusted by the maximum number of days of the new moon.

  2. Mysql> select DATE_ADD ('2017-01-30 ', Interval 1 month );
  3. -> 1998-02-28

Note: In the previous example, the INTERVAL and type keywords are not case sensitive. TO_DAYS (date) indicates a date, and returns a number of days (from 0 years ).

  2. Mysql> select TO_DAYS (950501 );
  3. -> 728779
  4. Mysql> select TO_DAYS ('2017-10-07 ');
  5. -> 729669

TO_DAYS () is not intended to be used to use the value before the occurrence of the glipro calendars (1582.

FROM_DAYS (N) returns a day N and a DATE value.

  2. Mysql> select FROM_DAYS (729669 );
  3. -> '2017-10-07'

TO_DAYS () is not intended to be used to use the value before the occurrence of the glipro calendars (1582.

DATE_FORMAT (date, format) format the date value based on the format string. The following modifier can be used in the format string: % M month name (January ...... December) % W name of the week (Sunday ...... Saturday) % D date of the month with an English prefix (1st, 2nd, 3rd, and so on .) % Y year, number, 4 digits % y year, number, 2 digits % a abbreviation of the week name (Sun ...... Sat) % d number of days in the month (00 ...... 31) % e number of days in the month (0 ...... 31) % m month, number (01 ...... 12) % c month, number (1 ...... 12) % B abbreviated month name (Jan ...... Dec) % j days in a year (001 ...... 366) % H Hour (00 ...... 23) % k hour (0 ...... 23) % h Hour (01 ...... 12) % I hour (01 ...... 12) % l hour (1 ...... 12) % I minute, 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 days in a week (0 = Sunday ...... 6 = Saturday) % U week (0 ...... 52), here Sunday is the first day of the week % u week (0 ...... 52), Monday is the first day of the week % a text "% ".

All other characters are not interpreted and copied to the result.

  2. Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % W % M % Y ');
  3. -> 'Saturday October 1997'
  4. Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',' % H: % I: % S ');
  5. -> '22: 23: 00'
  6. Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',
  7. '% D % y % a % d % m % B % J ');
  8. -> '4th 97 Sat 04 10 Oct 123'
  9. Mysql> select DATE_FORMAT ('2017-10-04 22:23:00 ',
  10. '% H % k % I % r % T % S % W ');
  11. -> '22 22 10 10:23:00 PM 22:23:00 6'

In MySQL3.23, % is required before the format modifier. In earlier MySQL versions, % is optional.

TIME_FORMAT (time, format) is used like the preceding DATE_FORMAT () function, but the format string can only contain format modifiers for processing hours, minutes, and seconds. Other modifiers generate 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. -> '2017-12-15'
  4. Mysql> select CURDATE () + 0;
  5. -> 19971215

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 in the context of a number.

  2. Mysql> select CURTIME ();
  3. -> '23: 50: 26'
  4. Mysql> select CURTIME () + 0;
  5. -> 235026

NOW () SYSDATE () CURRENT_TIMESTAMP returns the current date and time in 'yyyy-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS format, it depends on whether the function is used in a string or in the context of a number.

  2. Mysql> select NOW ();
  3. -> '2017-12-15 23:50:26'
  4. Mysql> select NOW () + 0;
  5. -> 19971215235026

UNIX_TIMESTAMP () UNIX_TIMESTAMP (date) If no parameter is called, a Unix time stamp (in seconds from '2017-01-01 00:00:00 'GMT) is returned ). If UNIX_TIMESTAMP () is called with a date parameter, it returns the second value starting from '2017-01-01 00:00:00 'GMT. Date can be a number of a DATE string, a DATETIME string, a TIMESTAMP, or a local time in YYMMDD or YYYYMMDD format. Mysql> select UNIX_TIMESTAMP ();

  2. -> 882226357
  3. Mysql> select UNIX_TIMESTAMP ('2017-10-04 22:23:00 ');
  4. -> 875996580

When UNIX_TIMESTAMP is used in a TIMESTAMP column, the function accepts 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 'yyyy-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

  2. Mysql> select FROM_UNIXTIME (875996580 );
  3. -> '2017-10-04 22:23:00'
  4. Mysql> select FROM_UNIXTIME (875996580) + 0;
  5. -> 19971004222300

FROM_UNIXTIME (unix_timestamp, format) returns a string representing the Unix time Mark, formatted according to the format string. Format can contain the same modifier as the entries listed by the DATE_FORMAT () function.

  2. Mysql> select FROM_UNIXTIME (UNIX_TIMESTAMP (),
  3. '% Y % D % M % h: % I: % s % X ');
  4. -> '2014 23rd December 03:43:30 x'

SEC_TO_TIME (seconds) returns the seconds parameter, which is converted to hour, minute, and second. The value is formatted in 'hh: MM: SS' or HHMMSS, it depends on whether the function is used in a string or a number context.

  2. Mysql> select SEC_TO_TIME (2378 );
  3. -> '00: 39: 38'
  4. Mysql> select SEC_TO_TIME (2378) + 0;
  5. -> 3938

TIME_TO_SEC (time) returns the time parameter, which is 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

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

Related Keywords:
php query function php function mysql query php date format function php date function php date function format current date function php php date function timezone

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

What's Trending
Top 10 Tags
datastax versions naming convention zookeeper client class definition md5 microsoft sql server 2005 data structures exception handling error handling
Top 10 Keywords
microsoft download center down wordpress address url site address url wordpress address url windows installer 4 0 download 302 not found web address url definition site address url wordpress db2 integer mac os installation step by step pdf abbreviation for return

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

  • Sales Support

    1 on 1 presale consultation

    Chat Contact Sales

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    Open a Ticket

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

    Learn More