Mysql Date and Time function _mysql

The following query selects the 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 are compliant with 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 (' 1998-02-03 22:23:00 ');
-> 1
Mysql> SELECT Weekday (' 1997-11-05 ');
-> 2
DayOfMonth (date)
Returns date is the first day of January, ranging from 1 to 31:
mysql> SELECT dayofmonth (' 1998-02-03 ');
-> 3
DayOfYear (date)
Returns date is the first 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 of date:
mysql> SELECT monthname ("1998-02-05");
-> ' February '
Quarter (date)
Returns date in the quarter of the year, with a range of 1 to 4:
Mysql> SELECT Quarter (' 98-04-01 ');
-> 2
WEEK (date)
WEEK (Date,first)
For the Sunday is the first day of the week, if the function has only one parameter call, return date is the week ordinal of the year, the return value range is 0 to 53 (yes, there may be the beginning of the 53rd week). The two-parameter WEEK () allows you to specify whether a week starts in Sunday or Monday and returns a value of 0-53 or 1-52. A table here shows how the second argument works: value meaning
01 weeks starting from Sunday, the return value range is 0-53
1 week starting in Monday, the return value range is 0-53
2 week starting in Sunday, the return value range is 1-53
31 weeks starting from 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, MySQL will return 0 if you do not use 2 or 3 as optional parameters:
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 the MySQL WEEK () function should return 52 when the given date value is actually part of the 52nd week of 1999. We decided to return 0 because we wanted the function to return "weeks in the specified year". This makes the use of the WEEK () function reliable when used in conjunction with the function of the month-day value in its extracted date value. If you prefer to get the appropriate year-week value, you should use 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 is the week of the year. The form and function of the second parameter are 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 the WEEK () returns the week value according to the given year context.
HOUR (Time)
Returns the time value of time, 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 second value of time, ranging from 0 to 59:
mysql> SELECT SECOND (' 10:05:03 ');
-> 3
Period_add (P,n)
Add N months to period P (format 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 P1 and P2. P1 and P2 should be specified in Yymm or yyyymm. Note that the period 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 arithmetic operations on dates. Adddate () and subdate () are synonyms for Date_add () and Date_sub () respectively. In MySQL 3.23, if the right side of the expression is a date value or a date-time field, you can use + and-instead of Date_add () and Date_sub () (see below for example). Parameter date is a DATETIME or date value that specifies the beginning of a date. Expr is an expression that specifies whether to increase 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 indicates that an expression is interpreted in Hoge form. The following table shows how the type and expr parameters are associated: Type value expr expected format
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
Day
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 as a delimiter. The suggested bounding characters are shown in the table. If the date parameter is a date value and the calculated interval has only the year, MONTH, and day portions (no time part), then the return value is also a date value. 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" 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 the intervals that are expected by the Type keyword), MySQL assumes that you have omitted the leftmost portion of the interval value. For example, if you specify a type as Day_second, the expr value is expected to contain the day, hour, minute, and second portions. If you specify a value like "1:10", MySQL assumes that the day and hour sections are omitted, and the specified values represent minutes and seconds. In other words, the "1:10" Day_second is interpreted as equivalent to the "1:10" Minute_second. This is similar to how MySQL interprets the time value as elapsed rather than the moment of day. Note that if you increase or decrease a date value according to the interval that contains one 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 return result will be NULL. If you add MONTH, Year_month, or year, and the resulting date is larger than the maximum number of days in the new month, 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 the keyword type are case-insensitive.

EXTRACT (type from date)
The EXTRACT () function uses the type of interval that is consistent with date_add () or date_sub (), but it is used to specify the part that is extracted from the date, rather than 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)
Gives a date dated, returning a number of days from the beginning of 0 years:
Mysql> SELECT to_days (950501);
-> 728779
mysql> SELECT to_days (' 1997-10-07 ');
-> 729669
To_days () is not intended to use the values that appear before the Gregorian calendar (that is, the current Gregorian calendar) (1582), because it does not consider the number of days that are lost when the calendar changes.

From_days (N)
Gives a number of days N, returning a DATE value:
Mysql> SELECT from_days (729669);
-> ' 1997-10-07 '
From_days () is not intended to use the value appearing 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 modifiers can be used in the format string: modifiers meaning
%m Month's name (January. December)
%w Week's name (Sunday. Saturday)
%d The first day of the month with the English suffix (0th, 1st, 2nd, 3rd, etc.)
%Y year, number, 4-digit
%y year, number, 2-digit
The year of the week value of%x, Sunday is the first day of one weeks, number, 4, with '%V ' use
The year of the week value of%x, Monday is the first day of one weeks, number, 4, with '%v ' use
%a name of the week (Sun. Sat)
Days in%d months, number (00..31)
%e the number of days in the month (0..31)
%m month, number of (00..12)
%c month, number of (0..12)
%b abbreviated month name. DEC)
%j 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 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 the number of days in a 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 a letter "%"
All other characters are copied directly to the result without explanation:
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 ');
-> ' 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 month and day modifiers range from zero because, in MySQL 3.23, it allows you to store imperfect date values (such as ' 2004-00-00 ').

Time_format (Time,format)
It is used in the same way as the Date_format () function above, but the format string contains only those formatting modifiers that handle hours, minutes, and seconds. Using other modifiers 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 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 a numeric 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 numeric 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, when the query starts executing. This means that if you refer to now () multiple times in a separate query, it will only give a value of the same time.

Unix_timestamp ()
Unix_timestamp (date)
Returns a Unix timestamp (the number of seconds since ' 1970-01-01 00:00:00 ' GMT) as an unsigned integer if there are no arguments at the call. If Unix_timestamp () is called with a parameter date, it returns the number of seconds that the parameter value starts passing through 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 implied "string-to-unix-timestamp" conversion. If you pass a date parameter that goes out of range to Unix_timestamp (), it will return 0, but note that MySQL only makes a basic test of it (the year range 1970-2037, month 01-12, date 01-31). If you want to subtract the Unix_timestamp () column, you should need to 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 ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format, depending on whether the function is used to make a string or a numeric context. If format is given, the return value is formatted according to the format string. The 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 ' HH:MM:SS ' or HHMMSS format, that the parameter seconds is converted to seconds, depending on whether the function is used to make 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)
Converts the parameter time to seconds and returns:
mysql> SELECT time_to_sec (' 22:23:00 ');
-> 80580
mysql> SELECT time_to_sec (' 00:39:38 ');
-> 2378