Date and time function applications in Mysql don't ask for favors _mysql

For a description of the range of values owned by each type and the valid format for the specified date and time value, see 7.3.6 Date and Time type.

Here is an example of using a date function. The following query selects all records and its date_col value is within the last 30 days:

Mysql> SELECT something from table
WHERE To_days (now ())-To_days (Date_col) <= 30;

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

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

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

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

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

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

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

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

WEEK (date)
　
WEEK (Date,first)
For Sunday is the first day of the week, there is a single parameter that returns the week number of date, ranging from 0 to 52. 2 Parameter Form week () allow
Do you specify whether the week starts in Sunday or Monday. If the second argument is 0, the week begins in 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

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

HOUR (Time)
Returns the hour of time, ranging from 0 to 23.
Mysql> Select HOUR (' 10:05:03 ');
-> 10

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

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

Period_add (P,n)
Increase n months to phase p (in format Yymm or yyyymm). Returns a value in YYYYMM format. Note that the phase parameter p is not a date value.
Mysql> Select Period_add (9801,2);
-> 199803

Period_diff (P1,P2)
Returns the number of months between periods P1 and P2, P1 and P2 should be in a format yymm or yyyymm. Note that the time parameter P1 and P2 are not date values.
Mysql> Select Period_diff (9802,199703);
-> 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 for 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 "-" start represents 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 type and expr parameters are associated: the desired expr format for type value meaning
SECOND seconds SECONDS
MINUTE minutes MINUTES
HOUR Time HOURS
Day days
MONTH Month MONTHS
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 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 separator in the expr format. Indicates that the suggested separator is displayed. If the date parameter is a date value and your calculation is only
Contains the year, month, and day sections (that is, there is no time part) 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 for the type keyword), MySQL assumes that you have omitted the leftmost portion of the interval value. For example
If you specify that a type is Day_second, the value expr is expected to have the day, hour, minute, and second portions. If you specify a value like "1:10",
MySQL assumes that the days and hours are partly missing and the values represent minutes and seconds. In other words, the "1:10" Day_second is equivalent to "1:10" Minute_second
Way to explain this to that MySQL explains that the time value represents elapsed times rather than as a day of time two semantic. If you use a date that is not really correct,
The result is null. If you add month, year_month or year and the result date is greater than the maximum number of days for the new month, the day is adjusted with the largest day in the crescent moon.

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

Note that from the previous example the words interval and type keywords are not case-sensitive.
To_days (date)
Gives a date dated, returning a number of days (from 0 years).
Mysql> Select To_days (950501);
-> 728779
Mysql> Select To_days (' 1997-10-07 ');
-> 729669

To_days () is not intended to use the value before Gregory (1582) occurs.

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

To_days () is not intended to use the value before Gregory (1582) occurs.

Date_format (Date,format)
Formats the date value based on 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 prefix (1st, 2nd, 3rd, and so on). ）
%Y years, numbers, 4-bit
%y years, numbers, 2-bit
%a name of the week (Sun ...) Sat)
Days in%d months, numbers (00 ...). 31)
%e the number of days in the month, numbers (0 ...). 31)
%m Month, number (01 ...) 12)
%c month, number (1 ...). 12)
%b abbreviated month name (...) DEC)
%j the 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 one weeks (0=sunday ...). 6=saturday)
%u weeks (0 .....) 52), here Sunday is the first day of the week
%u weeks (0 .....) 52), here Monday is the first day of the week
Percent% of a text "%".

All other characters do not interpret are copied into the result.

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 formatting modifiers. 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 modifiers that handle hours, minutes, and seconds.
Other modifiers produce a null value or 0.
Curdate ()
　
Current_date
Returns the Today 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

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

Now ()
　
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 in a string or a numeric
Context is used.
Mysql> Select Now ();
-> ' 1997-12-15 23:50:26 '
Mysql> Select now () + 0;
-> 19971215235026

Unix_timestamp ()
　
Unix_timestamp (date)
Returns a UNIX timestamp (the number of seconds since ' 1970-01-01 00:00:00 ' GMT) if no parameter calls are invoked. If Unix_timestamp () with a
The date parameter is called, which returns the number of seconds that start from ' 1970-01-01 00:00:00 ' GMT. Date can be a date string, a DateTime
A number that is a string, a timestamp, or a 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" 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 in a string
Or is used in a numeric context.
Mysql> Select From_unixtime (875996580);
-> ' 1997-10-04 22:23:00 '
Mysql> Select From_unixtime (875996580) + 0;
-> 19971004222300

From_unixtime (Unix_timestamp,format)
Returns a String representing the Unix time token, formatted according to the format string. FORMAT can contain the bars listed with the Date_format () function
The same modifier for the same purpose.
Mysql> Select From_unixtime (Unix_timestamp (),
'%Y%d%m%h:%i:%s%x ');
-> ' 1997 23rd December 03:43:30 X '

Sec_to_time (seconds)
Returns the seconds parameter, changing to hours, minutes, and seconds, and the value is formatted as ' 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

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