Home > Tutorials > PHP Tutorials

PHP Date Query Function utility code

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

  6. Date_default_timezone_set (' PRC '); Default time zone

  8. echo "Today:", Date ("Y-m-d", Time ()), "
    ";
  10. echo "Today:", Date ("Y-m-d", Strtotime ("June 2008")), "
    ";
  12. echo "Yesterday:", Date ("Y-m-d", Strtotime ("-1 Day")), "
    ";
  14. echo "Tomorrow:", Date ("Y-m-d", Strtotime ("+1 Day"), "
    ";
  16. echo "One week later:", Date ("Y-m-d", Strtotime ("+1 Week")), "
    ";
  18. echo "2 days a week four hours two seconds later:", Date ("Y-m-d g:h:s", Strtotime ("+1 Week 2 Day 4 hours 2 Seconds")), "
    ";
  20. echo "Next week Four:", Date ("Y-m-d", Strtotime ("next Thursday")), "
    ";
  22. echo "Previous Monday:". Date ("Y-m-d", Strtotime ("Last Monday")). "
    ";
  24. echo "One months ago:". Date ("Y-m-d", Strtotime ("Last month"). "
    ";
  26. echo "One months later:". Date ("Y-m-d", Strtotime ("+1 Month")). "
    ";
  28. echo "Ten years later:". Date ("Y-m-d", Strtotime ("+10 Year")). "
    ";
  30. $a = Date ("W", Time ()); Get today is what week of the week
  32. $startweekdate =date ("y-m-d h:i:s", Strtotime ($today)-($a-1) *24*3600); Get this week Monday
  34. $endweekdate =date ("y-m-d h:i:s", Strtotime ($today) + (7-$a + 1) *24*3600); Get the last day of the week and for the first day of next week
  36. $endnextweekdate =date ("y-m-d", Strtotime ($endweekdate) +7*24*3600);//The Last day of next week
  38. ?>
  40. $d = time (); Date to be processed

  42. $w = Date ("W", $d); It's a day of the week.

  44. $d 0 = Date ("y-m-d", Strtotime ("-$w Day", $d)); Week start
  46. $d 6 = date ("y-m-d", Strtotime ((6-$w). "Day", $d)); Week End
  48. ?>
  50. $d = time (); Date to be processed
  52. $w = Date ("W", $d); It's a day of the week.

  54. $d 0 = mktime (0,0,0,date ("M"), Date ("D")-$w, date ("Y"));//week start

  56. $d 0 = mktime (0,0,0,date ("M"), Date ("D")-$w + 6,date ("Y"));//End of week
  58. ?>
  60. $date = time ();

  62. $w = Date ("W", $d);

  64. $d 0 = Date ("y-m-d", Strtotime ("-$w Day", $date)); Week start
  66. $d 6 = date ("y-m-d", Strtotime ((6-$w). "Day", $date)); Week End
  68. ?>
  70. $date 1 = date ("y-m-d", Strtotime ("Sunday")); Week start
  72. $date 2 = Date ("y-m-d", Strtotime ("Saturday")); Week End
  74. $date 1 = date ("y-m-d", Strtotime ("Last Sunday")); Started last week
  76. $date 2 = Date ("y-m-d", Strtotime ("Last Saturday")); Ended last week
  78. To get the system in the month
  80. $tmp _date=date ("Ym");
  82. Cut out year
  84. $tmp _year=substr ($tmp _date,0,4);
  86. Cut out Month
  88. $tmp _mon =substr ($tmp _date,4,2);
  90. $tmp _nextmnoth=mktime (0,0,0, $tmp _mon+1,1, $tmp _year);
  92. $tmp _forwardmnoth=mktime (0,0,0, $tmp _mon-1,1, $tmp _year);
  94. Get the next month of the current month
  96. $FM _next_mnoth=date ("Ym", $tmp _nextmonth);
  98. Get the last one months of the current month
  100. $FM _forward_mnoth=date ("Ym", $tmp _forwardmonth);
  102. ?>
  104. $d =array ("Day", "one", "two", "three", "four", "five", "six");
  106. $whatday = "Week". $d [Date ("W", Strtotime ($today))]; Get today's Day of the week
  108. $time = ABS ((strtotime ("2008-12-25")-Strtotime (Date ("y-m-d"))/86400);/get the number of days difference between two dates
  110. ?>

Copy Code

See 7.3.6 date and time types for a description of the range of values that each type has and the valid format for the specified date and time value.

Here is an example of using a date function. Select All records whose 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;
Copy Code

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

    2. Mysql> Select DAYOFWEEK (' 1998-02-03 ');
    3. 3
Copy Code

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

    2. Mysql> Select WEEKDAY (' 1997-10-04 22:23:00 ');
    3. 5
    4. Mysql> Select WEEKDAY (' 1997-11-05 ');
    5. 2
Copy Code

DayOfMonth (date) Returns the date of the month in the range of 1 to 31.

    2. Mysql> Select DayOfMonth (' 1998-02-03 ');
    3. 3
Copy Code

DayOfYear (date) returns the number of days in a year, in the range of 1 to 366.

    2. Mysql> Select DayOfYear (' 1998-02-03 ');
    3. 34
Copy Code

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

    2. Mysql> Select MONTH (' 1998-02-03 ');
    3. 2
Copy Code

Dayname (date) returns the week name of date.

    2. Mysql> Select Dayname ("1998-02-05");
    3. ' Thursday '
Copy Code

MONTHNAME (date) returns the month name of date.

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

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

    2. Mysql> Select QUARTER (' 98-04-01 ');
    3. 2
Copy Code

WEEK (date) WEEK (Date,first), where Sunday is the first day of the week, has a single parameter that returns the number of weeks of the date, ranging from 0 to 52. 2 Parameter Form week () allows you to specify whether the week starts in Sunday or Monday. If the second parameter is 0, the week starts from Sunday, if the second parameter is 1, starting from Monday.

    2. Mysql> Select WEEK (' 1998-02-20 ');
    3. 7
    4. Mysql> Select WEEK (' 1998-02-20 ', 0);
    5. 7
    6. Mysql> Select WEEK (' 1998-02-20 ', 1);
    7. 8
Copy Code

Year (date) returns the years of date, ranging from 1000 to 9999.

    2. Mysql> Select year (' 98-02-03 ');
    3. 1998
Copy Code

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

    2. Mysql> Select HOUR (' 10:05:03 ');
    3. 10
Copy Code

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

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

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

    2. Mysql> Select SECOND (' 10:05:03 ');
    3. 3
Copy Code

Period_add (p,n) adds N months to the stage P (in format yymm or yyyymm). Returns a value in the format yyyymm. Note that the stage parameter p is not a date value.

    2. Mysql> Select Period_add (9801,2);
    3. 199803
Copy Code

Period_diff (P1,P2) returns the number of months between the period P1 and P2, P1 and P2 should be in the format yymm or yyyymm. Note that the time parameters P1 and P2 are not date values.

    2. Mysql> Select Period_diff (9802,199703);
    3. 11
Copy Code

Date_add (Date,interval expr type) date_sub (date,interval expr type) adddate (date,interval expr type) subdate (date,in Terval expr type) These functions perform a date operation. 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 example) date is a datetime or date value that specifies the start date, and expr is an expression that specifies the interval value to be subtracted from the start date or from the start date, and expr is a string; it can start with a "-" to indicate a negative interval. Type is a keyword that indicates how an 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: type value meaning expected expr format SECOND sec SECONDS MINUTE min MINUTES HOUR time HOURS day days months month MONTHS year Year years Minute_second minutes and seconds "minutes:seconds" hour_minute hours and minutes "hours:minutes" Day_hour Day and Hour "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 day, hour, minute, Seconds "Days HOURS:MINUTES:SECONDS"

MySQL allows any punctuation delimiter in the expr format. Indicates that the suggested delimiter is displayed. If the date parameter is a date value and your calculation contains only the year, month, and day portions (that is, there is no time part), the result is a date value. Otherwise, the result is a datetime value.

  4. mysql> Select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
  6. -1998-01-01 00:00:00
  8. Mysql> Select INTERVAL 1 day + "1997-12-31";
  10. 1998-01-01
  12. mysql> Select "1998-01-01"-INTERVAL 1 SECOND;
  14. -1997-12-31 23:59:59
  16. Mysql> Select Date_add ("1997-12-31 23:59:59",
  18. INTERVAL 1 SECOND);
  20. -1998-01-01 00:00:00
  22. Mysql> Select Date_add ("1997-12-31 23:59:59",
  24. INTERVAL 1 day);
  26. -1998-01-01 23:59:59
  28. Mysql> Select Date_add ("1997-12-31 23:59:59",
  30. INTERVAL "1:1" minute_second);
  32. -1998-01-01 00:01:00
  34. Mysql> Select Date_sub ("1998-01-01 00:00:00",
  36. INTERVAL "1 1:1:1" day_second);
  38. -1997-12-30 22:58:59
  40. Mysql> Select Date_add ("1998-01-01 00:00:00",
  42. INTERVAL "-1" day_hour);
  44. -1997-12-30 14:00:00
  46. Mysql> Select Date_sub ("1998-01-02", INTERVAL);
  48. 1997-12-02
  50. Mysql> Select EXTRACT (Year from "1999-07-02");
  52. 1999
  54. Mysql> Select EXTRACT (year_month from "1999-07-02 01:02:03");
  56. 199907
  58. Mysql> Select EXTRACT (day_minute from "1999-07-02 01:02:03");
  60. 20102
Copy Code

If you specify an interval value that is too short (excluding the desired interval portion of 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 the day, hour, minute, and second parts. If you specify a value like "1:10", MySQL assumes that the day and hour portions are missing and the values represent minutes and seconds. In other words, the "1:10" Day_second is interpreted in the same way that it is equivalent to "1:10" minute_second, which means that MySQL interprets the time value as being two semantic in the way it has elapsed rather than as a day. If you use a date that is actually incorrect, the result is null. If you increase month, year_month, or year and the result date is greater than the maximum number of days for the new month, the days are adjusted with the largest day in the crescent.

    2. Mysql> Select Date_add (' 1998-01-30 ', Interval 1 month);
    3. 1998-02-28
Copy Code

Note that from the previous example morphemes interval and type keywords are not case-sensitive. To_days (date) gives a date, which returns a number of days (from 0 years).

    2. Mysql> Select To_days (950501);
    3. 728779
    4. Mysql> Select To_days (' 1997-10-07 ');
    5. 729669
Copy Code

To_days () does not intend to use the value before the Gregory (1582) appears.

From_days (n) gives a number of days N, which returns a date value.

    2. Mysql> Select From_days (729669);
    3. ' 1997-10-07 '
Copy Code

To_days () does not intend to use the value before the Gregory (1582) appears.

Date_format (Date,format) formats the date value according to the format string. The following modifiers can be used in the format string:%M month name (January ... December)%W week name (Sunday ... Saturday)%d The date of the month with English prefixes (1st, 2nd, 3rd, etc.). )%Y year, number, 4-digit%Y year, number, 2-bit%a abbreviated weekday name (Sun ... Sat)%d days in the month, number (00 ... %e number of days in the month, number (0 ... %m months, numbers (01 ... %c month, number (1 ... %b Abbreviated month name (Jan ... DEC)%j Days of the year (001 ... 366)%H hours (00 ... %k hours (0 ...) %h hours (01 ...) %I) Hours (01 ... %l hours (1 ...) %i minutes, Numbers (00 ...) %r time, 12 hours (Hh:mm:ss [ap]m)%T time, 24 hours (hh:mm:ss)%s seconds (00 ... ()%s seconds (00 ... %p am or PM%w one days of the week (0=sunday ... 6=saturday)%u week (0 ... 52), here Sunday is the first day of the week%u week (0 ... 52), here Monday is the first day of the week with a text "%".

All other characters are not interpreted as being copied into the results.

    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 Sat Oct 277 '
    9. Mysql> Select Date_format (' 1997-10-04-22:23:00 ',
    10. '%H%k%I%r%T%s%w ');
    11. -10:23:00 PM 22:23:00 00 6 '
Copy Code

In MySQL3.23,% is required before the format modifier character. In the earlier version of MySQL,% is optional.

Time_format (Time,format) is used like the Date_format () function above, but the format string can contain only those formatting 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 a 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 the ' 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
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 is used in a string or in the context of a number.

    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) returns a UNIX timestamp (the number of seconds from ' 1970-01-01 00:00:00 ' GMT) if no argument is called. If Unix_timestamp () is called with a date parameter, it returns the value of the second starting at ' 1970-01-01 00:00:00 ' GMT. Date can be a date string, a DateTime string, a timestamp, or a number in the 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 in a timestamp column, the function will accept the value directly, without the implied "String-to-unix-timestamp" transform.

From_unixtime (Unix_timestamp) returns the value represented by the YYYYMMDDHHMMSS parameter in the ' Yyyy-mm-dd HH:MM:SS ' or unix_timestamp format, depending on whether the function is in a string or used in a numeric context.

    2. Mysql> Select From_unixtime (875996580);
    3. ' 1997-10-04 22:23:00 '
    4. Mysql> Select From_unixtime (875996580) + 0;
    5. 19971004222300
Copy Code

From_unixtime (Unix_timestamp,format) returns a String representing the Unix time tag, formatted according to the format string. FORMAT can contain the same modifiers as the entries listed in the Date_format () function.

    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, changing to hours, minutes, and seconds, formatted with a value of ' HH:MM:SS ' or HHMMSS, depending on whether the function is used in a string or in a numeric 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) returns 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

    • 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 current date function php php date function timezone php date function format

    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