Mysql in the date-related function (SQL)

2. DAYOFWEEK (date)
   Returns the day of the Week index (1= Sunday, 2 = Monday, ... 7= Saturday). These index values correspond to the ODBC standard.
   Mysql> Select DAYOFWEEK (' 1998-02-03 ');
   3

4. DayOfMonth (date)
   Returns the date of the month in the range of 1 to 31.
   Mysql> Select DayOfMonth (' 1998-02-03 ');
   3

6. DayOfYear (date)
   Returns the number of days in a year, in the range of 1 to 366.
   Mysql> Select DayOfYear (' 1998-02-03 ');
   34

8. WEEKDAY (date)
   Returns 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

10. MONTH (date)
    Returns the month of date, ranging from 1 to 12.
    Mysql> Select MONTH (' 1998-02-03 ');
    2

12. Dayname (date)
    Returns the day of the week name of the date.
    Mysql> Select Dayname ("1998-02-05");
    ' Thursday '

14. MONTHNAME (date)
    Returns the month name of the date.
    Mysql> Select MONTHNAME ("1998-02-05");
    ' February '

16. QUARTER (date)
    Returns the quarter of a date year, ranging from 1 to 4.
    Mysql> Select QUARTER (' 98-04-01 ');
    2

18. WEEK (date)

    WEEK (Date,first)
    For Sunday is the first day of the week, there is a single parameter that returns the number of weeks of the date, ranging from 0 to 52. 2 parameter Forms week () allow
    Do you specify whether the week starts in Sunday or Monday. If the second argument is 0, the week starts from Sunday, and if the second argument is 1,
    Starting from Monday.
    Mysql> Select WEEK (' 1998-02-20 ');
    7
    Mysql> Select WEEK (' 1998-02-20 ', 0);
    7
    Mysql> Select WEEK (' 1998-02-20 ', 1);
    8

20. Year (date)
    Returns the year of date, ranging from 1000 to 9999.
    Mysql> Select year (' 98-02-03 ');
    1998

22. HOUR (Time)
    Returns the hour of time with a range of 0 to 23.
    Mysql> Select HOUR (' 10:05:03 ');
    10

24. MINUTE (Time)
    Returns the minute of time, ranging from 0 to 59.
    Mysql> Select MINUTE (' 98-02-03 10:05:03 ');
    5

26. SECOND (Time)
    The number of seconds to return time, ranging from 0 to 59.
    Mysql> Select SECOND (' 10:05:03 ');
    3

28. Period_add (P,n)
    Add 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.
    Mysql> Select Period_add (9801,2);
    199803

30. 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.
    Mysql> Select Period_diff (9802,199703);
    11

31. 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 example) date is a specified start date.
    datetime or Date value, expr is an expression that specifies an interval value that is added to the start date or subtracted from the start date, and expr is a string;
    A "-" begins to indicate a negative interval. Type is a keyword that indicates how an expression should be interpreted. EXTRACT (type from date) function from date
    Returns the "type" interval in the The following table shows how the type and expr parameters are associated: The type value means the desired expr format
    SECOND sec SECONDS
    MINUTE min MINUTES
    HOUR Time HOURS
    Day days
    Month MONTHS
    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 day, hour, minute "Days Hours:minutes"
    Day_second day, hour, minute, second "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 is only
    Contains the year, month, and day portions (that is, there is no time section) and the result is a date value. Otherwise, the result is a datetime value.

    mysql> Select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
    -1998-01-01 00:00:00
    mysql> Select INTERVAL 1 day + "1997-12-31";
    1998-01-01
    mysql> Select "1998-01-01"-INTERVAL 1 SECOND;
    -1997-12-31 23:59:59
    mysql> Select Date_add ("1997-12-31 23:59:59",
    INTERVAL 1 SECOND);
    -1998-01-01 00:00:00
    mysql> Select Date_add ("1997-12-31 23:59:59",
    INTERVAL 1 day);
    -1998-01-01 23:59:59
    Mysql> Select Date_add ("1997-12-31 23:59:59",
    INTERVAL "1:1" minute_second);
    -1998-01-01 00:01:00
    mysql> Select Date_sub ("1998-01-01 00:00:00",
    INTERVAL "1 1:1:1" day_second);
    -1997-12-30 22:58:59
    mysql> Select Date_add ("1998-01-01 00:00:00",
    INTERVAL "-1" day_hour);
    -1997-12-30 14:00:00
    mysql> Select Date_sub ("1998-01-02", INTERVAL);
    1997-12-02
    mysql> Select EXTRACT (Year from "1999-07-02");
    1999
    Mysql> Select EXTRACT (year_month from "1999-07-02 01:02:03");
    199907
    mysql> Select EXTRACT (day_minute from "1999-07-02 01:02:03");
    20102

    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 equivalent to "1:10" Minute_second
    The way to explain this to that MySQL explains that the time value represents the way elapsed rather than as the time of day has two semantics. If you use a date that is really 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.

    Mysql> Select Date_add (' 1998-01-30 ', Interval 1 month);
    1998-02-28

    Note that from the previous example morphemes interval and type keywords are not case-sensitive.

33. To_days (date)
    Gives a date, which returns a number of days (from 0 years).
    Mysql> Select To_days (950501);
    728779
    Mysql> Select To_days (' 1997-10-07 ');
    729669

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

35. From_days (N)
    Gives a number of days N, which returns a date value.
    Mysql> Select From_days (729669);
    ' 1997-10-07 '

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

37. 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 bit
    %y year, number, 2 bit
    %a abbreviated weekday name (Sun ... Sat)
    Number of days in the month of%d, number (00 ...). 31)
    Number of days in%e month, number (0 ... 31)
    %m Month, number (01 ... 12)
    %c month, number (1 ... 12)
    %b Abbreviated month name (Jan ... DEC)
    %j Days of the 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, Numbers (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 one weeks (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
    Percent of a text "%".

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

    mysql> Select Date_format (' 1997-10-04 22:23:00 ', '%W%M%Y ');
    -Saturday October 1997 '
    mysql> select Date_format ( ' 1997-10-04 22:23:00 ', '%h:%i:%s ');
    -22:23:00 '
    mysql> Select date_format ( ' 1997-10-04 22:23:00 ',
    '%d%y%a%d%m%b%j ');
    -4th Sat Oct 277 '
    mysql> Select Date_format (' 1997-10-04 22:23:00 ',
    '%H%k%I%r%T%s%w ');
    -10:23:00 PM 22:23:00 6 '
    MySQL3.23,% is required before the format modifier character. In the earlier version of MySQL,% is optional.

    time_format (Time,format)
    This is used like the Date_format () function above, but the format string can contain only those formatting decorations that handle hours, minutes, and seconds.

40. Curdat () 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.
    Mysql> select Curdate ();
    ' 1997-12-15 '
    Mysql> Select curdate () + 0;
    19971215
42. Curtime ()

44. 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.
    Mysql> select Curtime ();
    ' 23:50:26 '
    Mysql> Select Curtime () + 0;
    235026

46. Now ()

48. Sysdate ()

    Current_timestamp
    Returns the current date and time in the ' 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.
    Mysql> Select Now ();
    ' 1997-12-15 23:50:26 '
    Mysql> Select now () + 0;
    19971215235026

50. Unix_timestamp ()

52. 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 ();
    882226357
    Mysql> Select Unix_timestamp (' 1997-10-04 22:23:00 ');
    875996580

    When Unix_timestamp is used in a timestamp column, the function will accept the value directly, without the implied "String-to-unix-timestamp" transform.

54. From_unixtime (Unix_timestamp)
    Returns the value represented by the Unix_timestamp parameter in the ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format, depending on whether the function is in a string
    or used in a numeric context.
    Mysql> Select From_unixtime (875996580);
    ' 1997-10-04 22:23:00 '
    Mysql> Select From_unixtime (875996580) + 0;
    19971004222300

56. From_unixtime (Unix_timestamp,format)
    Returns a String representing the Unix time token, formatted according to the format string. FORMAT can contain the same modifiers as the entries listed in the Date_format () function.
    Mysql> Select From_unixtime (Unix_timestamp (),
    '%Y%d%M%h:%i:%s%x ');
    ' 1997 23rd December 03:43:30 X '

58. 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 in a string or a number
    is used in the context.
    Mysql> Select Sec_to_time (2378);
    ' 00:39:38 '
    Mysql> Select Sec_to_time (2378) + 0;
    3938
60. Time_to_sec (Time)
    Returns the time parameter, converted to seconds.
    Mysql> Select Time_to_sec (' 22:23:00 ');
    80580
    Mysql> Select Time_to_sec (' 00:39:38 ');
    2378
62. 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, number, 4-bit
    %Y year, number, 2-bit
    % a abbreviated weekday name (Sun ... Sat)
    Number of days in the month of%d (00 ... )
    Number of days in the%e month, number (0 ... )
    %m Month, number (01 ...
    %c Month, number (1 ...
    %b Abbreviated month name (Jan ... DEC)
    %j number of days in the year (001 ... 366)
    % h hours (00 ... )
    %k hours (0 ... )
    %h hours (01 ... )
    %I Hours (01 ...
    % L hours (1 ...)
    % i minutes, number (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 the number of days in one weeks (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
    %% a text "%". All other characters are not interpreted as being copied into the results.

64. Mysql> Select Date_format (' 1997-10-04 22:23:00 ', '%W%M%Y ');
    ' Saturday October 1997 '
    Mysql> Select Date_format (' 1997-10-04 22:23:00 ', '%h:%i:%s ');
    ' 22:23:00 '
    Mysql> Select Date_format (' 1997-10-04-22:23:00 ',
    '%d%y%a%d%m%b%j ');
    4th Sat Oct 277 '
    Mysql> Select Date_format (' 1997-10-04-22:23:00 ',
    '%H%k%I%r%T%s%w ');
    -10:23:00 PM 22:23:00 00 6 '
    In MySQL3.23,% is required before the format modifier character. In the earlier version of MySQL,% is optional.

66. Time_format (Time,format)
    This 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.
68. Curdate ()

70. 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.
    Mysql> select Curdate ();
    ' 1997-12-15 '
    Mysql> Select curdate () + 0;
    19971215

72. Curtime ()

74. 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.
    Mysql> select Curtime ();
    ' 23:50:26 '
    Mysql> Select Curtime () + 0;
    235026

76. Now ()
78. Sysdate ()
80. Current_timestamp
    Returns the current date and time in ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format, depending on whether the function is in a string or a number
    Context is used.
    Mysql> Select Now ();
    ' 1997-12-15 23:50:26 '
    Mysql> Select now () + 0;
    19971215235026
82. Unix_timestamp ()

83. 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 ();
    882226357
    Mysql> Select Unix_timestamp (' 1997-10-04 22:23:00 ');
    875996580

    When Unix_timestamp is used in a timestamp column, the function will accept the value directly, without the implied "String-to-unix-timestamp" transform www.knowsky.com.

    From_unixtime (Unix_timestamp)
    Returns the value represented by the Unix_timestamp parameter in the ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or in a numeric context.
    Mysql> Select From_unixtime (875996580);
    ' 1997-10-04 22:23:00 '
    Mysql> Select From_unixtime (875996580) + 0;
    19971004222300

85. From_unixtime (Unix_timestamp,format)
    Returns a String representing the Unix time token, formatted according to the format string. FORMAT can contain the same modifiers as the entries listed in the Date_format () function.
    Mysql> Select From_unixtime (Unix_timestamp (),
    '%Y%d%M%h:%i:%s%x ');
    ' 1997 23rd December 03:43:30 X '
87. 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.
    Mysql> Select Sec_to_time (2378);
    ' 00:39:38 '
    Mysql> Select Sec_to_time (2378) + 0;
    3938
89. Time_to_sec (Time)
    Returns the time parameter, converted to seconds.
    Mysql> Select Time_to_sec (' 22:23:00 ');
    80580
    Mysql> Select Time_to_sec (' 00:39:38 ');
    2378

Mysql in the date-related function (SQL)