Here is an example of using a date function. The following query selects records for all Date_col values within the last 30 days.
Mysql> SELECT something from Tbl_name
WHERE To_days (now ()) –to_days (date_col) <= 30;
DAYOFWEEK (date)
Returns the week index of date (1 = Sunday, 2 = Monday, ...). 7 = Saturday). Index values conform to the ODBC standard.
mysql> SELECT DAYOFWEEK (' 1998-02-03′);
3
WEEKDAY (date)
Returns the week index of date (0 = Monday, 1 = Tuesday, ...). 6 = Sunday):
mysql> SELECT WEEKDAY (' 1998-02-03 22:23:00′);
1
mysql> SELECT WEEKDAY (' 1997-11-05′);
2
DayOfMonth (date)
The return date is the day ordinal of January, ranging from 1 to 31:
mysql> SELECT dayofmonth (' 1998-02-03′);
3
DayOfYear (date)
Return date is the day of the year, ranging from 1 to 366:
mysql> SELECT dayofyear (' 1998-02-03′);
34
MONTH (date)
Returns the month in date, ranging from 1 to 12:
mysql> SELECT MONTH (' 1998-02-03′);
2
Dayname (date)
Returns the week name of date:
Mysql> Select Dayname ("1998-02-05″");
' Thursday '
MONTHNAME (date)
Returns the month name for date:
Mysql> Select MONTHNAME ("1998-02-05″");
' February '
QUARTER (date)
Returns a date in the quarter of a 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, if the function has only one parameter call, the return date is the week ordinal of the year, and the return value range is 0 to 53 (yes, there may be the beginning of the 53rd week). The two-Parameter form of WEEK () allows you to specify whether a week starts in Sunday or Monday, and the return value is 0-53 or 1-52. Here's a table showing how the second parameter works: value meaning
01 weeks starting in Sunday, the return value range is 0-53
1 a week starting in Monday, the return value range is 0-53
2 a week starting in Sunday, the return value range is 1-53
31 weeks starting in Monday, the return value range is 1-53 (ISO 8601)
mysql> SELECT WEEK (' 1998-02-20′);
7
mysql> SELECT WEEK (' 1998-02-20′,0);
7
mysql> SELECT WEEK (' 1998-02-20′,1);
8
mysql> SELECT WEEK (' 1998-12-31′,1);
53
Note that in version 4.0, WEEK (#,0) was changed to match the USA calendar. Note that if the week is the last week of the previous year, when you do not use 2 or 3 as an optional parameter, MySQL will return 0:
mysql> SELECT Year (' 2000-01-01′), WEEK (' 2000-01-01′,0);
--2000, 0
mysql> SELECT WEEK (' 2000-01-01′,2);
52
You might argue that when a given date value is actually part of the 52nd Week of 1999, MySQL should return 52 to the WEEK () function. We decided to return 0 because we wanted the function to return "week of the specified year". This makes the use of the WEEK () function reliable when used in conjunction with other functions that extract the day value of a date value. If you prefer to get the appropriate year-week value, you should use the parameter 2 or 3 as an optional parameter, or use the function Yearweek ():
mysql> SELECT Yearweek (' 2000-01-01′);
199952
Mysql> SELECT MID (Yearweek (' 2000-01-01′), 5,2);
52
Year (date)
Returns the year of date with a range of 1000 to 9999:
mysql> SELECT Year (' 98-02-03′);
1998
Yearweek (date)
Yearweek (Date,first)
Returns a date value which week of the year. The form and function of the second parameter is exactly the same as the second parameter of WEEK (). Note that for a given date parameter is the first or last week of the year, the returned year value may be inconsistent with the year given by the date parameter:
mysql> SELECT Yearweek (' 1987-01-01′);
198653
Note that for optional parameters 0 or 1, the return value of the week value differs from the value returned by the WEEK () function (0), and WEEK () returns the week value based on the given year context.
HOUR (Time)
Returns the time value, ranging from 0 to 23:
mysql> SELECT HOUR (' 10:05:03′);
10
MINUTE (Time)
Returns the minute value of time, ranging from 0 to 59:
mysql> SELECT MINUTE (' 98-02-03 10:05:03′);
5
SECOND (Time)
Returns the seconds value of time, ranging from 0 to 59:
mysql> SELECT SECOND (' 10:05:03′);
3
Period_add (P,n)
Add N months to the period P (in the form yymm or yyyymm). Returns a value in YYYYMM format. Note that the period parameter P is not a date value:
Mysql> SELECT Period_add (9801,2);
199803
Period_diff (P1,P2)
Returns the number of months between the period P1 and P2. P1 and P2 should be specified with Yymm or yyyymm. Note that the time parameters 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 arithmetic operations on dates. Adddate () and subdate () are synonyms of date_add () and Date_sub () respectively. In MySQL 3.23, if the right side of the expression is a date value or a datetime field, you can use + and – instead of Date_add () and Date_sub () (for example, below). The parameter date is a DATETIME or date value that specifies the beginning of a date. Expr is an expression that specifies whether to increment or subtract the interval value from the start date. expr is a string; it can represent a negative interval value with a "-" lead. Type is a keyword that signifies that the expression is interpreted in including. The following table shows how the type and expr parameters are associated: The type value expr expects format
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
Day days
MONTH MONTHS
Year years
Minute_second "Minutes:seconds"
Hour_minute "Hours:minutes"
Day_hour "Days HOURS"
Year_month "Years-months"
Hour_second "HOURS:MINUTES:SECONDS"
Day_minute "Days Hours:minutes"
Day_second "Days HOURS:MINUTES:SECONDS"
In the format of expr, MySQL allows any character to be used as a delimiter. The recommended bounding characters are shown in the table. The return value is also a date value if the date parameter is a date value and the calculated interval has only the year, MONTH, and day portions (no time portion). Otherwise the return value 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 10″day_hour);
-1997-12-30 14:00:00
Mysql> Select Date_sub ("1998-01-02″, INTERVAL);
1997-12-02
If you specify an interval value that is too short (not including all of the interval parts expected by the type keyword), MySQL assumes that you have omitted the leftmost part of the interval value. For example, if you specify a type of Day_second, the expr value is expected to contain the day, hour, minute, and second parts. If you specify a value like "1:10″", MySQL assumes that the day and hour parts are omitted, and the specified value represents minutes and seconds. In other words, "1:10″day_second is interpreted as equivalent to" 1:10″minute_second. This is similar to MySQL explaining that the time value is elapsed rather than a day. Note that if you increase or decrease a date value according to the interval that contains a time part, the date value is automatically converted to a DateTime value:
Mysql> Select Date_add ("1999-01-01″, INTERVAL 1 day);
1999-01-02
Mysql> Select Date_add ("1999-01-01″, INTERVAL 1 HOUR);
-1999-01-01 01:00:00
If you use a date that is not correct, the returned result will be NULL. If you increase month, year_month, or year, and the resulting date is larger than the maximum number of days in the new month, then it will be adjusted to the maximum number of days for the new month:
mysql> SELECT date_add (' 1998-01-30′, INTERVAL 1 MONTH);
1998-02-28
Note that in the above example, the word INTERVAL and keyword type are not case-sensitive.
EXTRACT (type from date)
The EXTRACT () function uses an interval type that is consistent with date_add () or date_sub (), but it is used to specify the part to extract from the date instead of the date arithmetic operation.
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
To_days (date)
Given a date, returns 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 values that appear before the Gregorian calendar (i.e. the current solar calendar) (1582), since it does not consider the number of days lost when the calendar changes.
From_days (N)
Given a number of days N, returns a DATE value:
Mysql> SELECT from_days (729669);
' 1997-10-07′
From_days () is not intended to use values that appear before the Gregorian calendar (1582) because it does not consider the number of days lost when the calendar changes.
Date_format (Date,format)
Formats the date value according to the format string. The following modifier can be used in the format string: modifier meaning
%M Month's name (January. December)
%W Week's name (Sunday. Saturday)
%d The day ordinal of a month with an English suffix (0th, 1st, 2nd, 3rd, etc)
%Y year, digital, 4-bit
%y year, digital, 2-bit
The year of the week value of%x, Sunday is the first day of one weeks, digital, 4-bit, used with '%V '
The year of the week value of%x, Monday is the first day of one weeks, digital, 4-bit, used with '%v '
%a abbreviated weekday name (Sun. Sat)
Number of days in the month of%d, numeric (00..31)
%e number of days in the month, number (0..31)
%m month, number of (00..12)
%c month, number of (0..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, number of (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 of the Week (0=sunday). 6=saturday)
%u week (00..53), Sunday is the first day of one weeks
%u week (00..53), Monday is the first day of one weeks
%V Week (01..53), Sunday is the first day of one weeks. Use with '%x '
%v Week (01..53), Monday is the first day of one weeks. Use with '%x '
Percent of one letter "%"
All other characters are not interpreted and copied directly 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′
mysql> SELECT date_format (' 1999-01-01′, '%x%V ');
' 1998 52′
In MySQL 3.23, the character '% ' is required before the format modifier. In earlier versions of MySQL, '% ' is optional. The zero-based range of month and day modifiers is because, in MySQL 3.23, it allows you to store imperfect date values (for example, ' 2004-00-00′ ').
Time_format (Time,format)
It is used in a similar way to the above date_format () function, but the format string contains only those formatting modifiers that handle hours, minutes, and seconds. Using a different modifier results in a NULL value or 0.
Curdate ()
Current_date
Returns the current date value in ' Yyyy-mm-dd ' or YYYYMMDD format, depending on whether the function is used in a string or in a digital 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 a digital context:
Mysql> SELECT curtime ();
' 23:50:26′
Mysql> SELECT curtime () + 0;
235026
Now ()
Sysdate ()
Current_timestamp
Returns the current datetime value in ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or in a digital context:
Mysql> SELECT now ();
' 1997-12-15 23:50:26′
Mysql> SELECT Now () + 0;
19971215235026
Note that the function now () is evaluated only once in each query, that is, when the query starts executing. This means that if now () is referenced more than once in a single query, it will only give the value the same time.
Unix_timestamp ()
Unix_timestamp (date)
If no arguments are called, returns a Unix timestamp as an unsigned integer (the number of seconds starting from ' 1970-01-01 00:00:00′gmt). If Unix_timestamp () is called with a parameter date, it returns the number of seconds that the parameter value has passed since the ' 1970-01-01 00:00:00′gmt. Date can be a date string, a DATETIME string, a TIMESTAMP, or a local time displayed as a YYMMDD or YYYYMMDD:
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 returns an internal timestamp value directly without an implicit "string-to-unix-timestamp" conversion. If you pass an out-of-range date parameter to Unix_timestamp (), it will return 0, but please note that MySQL only carries out a basic test (year range 1970-2037, month 01-12, date 01-31). If you want to subtract the Unix_timestamp () column, you should cast the result to a signed integer. View Chapter 6.3.5 Cast function.
From_unixtime (Unix_timestamp [, format])
Returns a Unix_timestamp parameter value in the ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format, depending on whether the function is used for a string or a numeric context. If format is given, the return value is formatted according to the format string. Format can contain the same modifiers as the Date_format () function.
Mysql> SELECT from_unixtime (875996580);
' 1997-10-04 22:23:00′
Mysql> SELECT From_unixtime (875996580) + 0;
19971004222300
Mysql> SELECT From_unixtime (Unix_timestamp (),
'%Y%d%M%h:%i:%s%x ');
' 1997 23rd December 03:43:30 1997′
Sec_to_time (seconds)
Returns the value in the ' HH:MM:SS ' or HHMMSS format when the parameter seconds is converted to seconds and minutes, depending on whether the function is used for a string or a numeric context:
Mysql> SELECT sec_to_time (2378);
' 00:39:38′
Mysql> SELECT Sec_to_time (2378) + 0;
3938
Time_to_sec (Time)
Returns the parameter time after it is converted to a number of seconds:
mysql> SELECT time_to_sec (' 22:23:00′);
80580
mysql> SELECT time_to_sec (' 00:39:38′);
2378
Describe MySQL's date and time functions
