Common date comparison and calculation functions in MySQL

Implementation of time comparison in MySql

unix_timestamp()

The unix_timestamp function can take one argument or no arguments.

Its return value is an unsigned integer. Without parameters, it returns the number of seconds that have passed since 00:00:00, January 1, 1970,

If a parameter is used and is of a time type or a string representation of a time type, it is the number of seconds from 1970-01-01 00:00:00 to the specified time.

With this function, it is natural to convert the time comparison into an unsigned integer comparison.

For example, determine whether a time is within a range

unix_timestamp( time ) between unix_timestamp( 'start ') and unix_timestamp( 'end' )

Here is an example using the date functions.

The following query selects all records where the value of date_col is within the last 30 days:

mysql> SELECT something FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

DAYOFWEEK(date)

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

mysql> select DAYOFWEEK('1998-02-03');
-> 3

WEEKDAY(date)

Returns the day of 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 day of the month in date, in the range 1 to 31.

mysql> select DAYOFMONTH('1998-02-03');
-> 3

DAYOFYEAR(date)

Returns the day of the year for date, in the range 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 day of the week of date.

mysql> select DAYNAME("1998-02-05");
-> 'Thursday'

MONTHNAME(date)

Returns the name of the month in date.

mysql> select MONTHNAME("1998-02-05");
-> 'February'

QUARTER(date)

Returns the quarter of the year for date, in the range 1 to 4.

mysql> select QUARTER('98-04-01');
-> 2

WEEK(date)

WEEK(date,first)

For places where Sunday is the first day of the week, there is a single argument that returns the week number of date, in the range 0 to 52. The 2-argument form WEEK() allows you to specify whether the week starts on Sunday or Monday. If the second argument is 0, the week starts on Sunday, if the second argument is 1, the week starts on 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, in the range 1000 to 9999.

mysql> select YEAR('98-02-03');
-> 1998

HOUR(time)

Returns the hour of time, in the range 0 to 23.

mysql> select HOUR('10:05:03');
-> 10

MINUTE(time)

Returns the minute of time, ranging from 0 to 59.

mysql> select MINUTE('98-02-03 10:05:03');
-> 5

SECOND(time)

Returns the seconds of time, ranging from 0 to 59.

mysql> select SECOND('10:05:03');
-> 3

PERIOD_ADD(P,N)

Add N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. 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 the format YYMM or YYYYMM. Note that the period 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 date arithmetic. As of 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 DATETIME or DATE value that specifies the starting date, expr is an expression that specifies the interval value to be added to or subtracted from the starting date, and expr is a string; it can begin with a "-" to indicate a negative interval. type is a keyword that specifies how the expression should be interpreted. The EXTRACT(type FROM date) function returns the interval "type" from a date. The following table shows how the type and expr parameters are associated: type value meaning expected expr format

SECOND seconds
MINUTE minutesMINUTES
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 days and hours "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 days, hours, minutes, seconds "DAYS HOURS:MINUTES:SECONDS"

MySQL allows any punctuation separators in the expr format. Indicates that the suggested separator is displayed. If the date argument is a DATE value and your calculation contains only YEAR, MONTH, and DAY parts (that is, no time portion), 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 10" DAY_HOUR);
-> 1997-12-30 14:00:00
mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
-> 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 (does not include the interval part expected by 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 of expr is expected to have day, hour, minute, and second components. If you specify a value like "1:10",

MySQL assumes that the day and hour parts are missing and that the value represents minutes and seconds. In other words, "1:10" DAY_SECOND is equivalent to "1:10"

MINUTE_SECOND , which is ambiguous about the way MySQL interprets TIME values ​​as representing elapsed time rather than as a time of day. If you use a date that is truly incorrect,

The result is NULL. If you increment MONTH, YEAR_MONTH, or YEAR and the resulting date is greater than the maximum number of days in the new month, the day is adjusted to use the maximum number of days in the new month.

mysql> select DATE_ADD('1998-01-30', Interval 1 month);
-> 1998-02-28

Note from the previous examples that the word INTERVAL and the type keyword are not case sensitive.

TO_DAYS(date)

Given a date date, returns a day number (the number of days since year 0).

mysql> select TO_DAYS(950501);
-> 728779
mysql> select TO_DAYS('1997-10-07');
-> 729669

FROM_DAYS(N)

Given a day number N, returns a DATE value.

mysql> select FROM_DAYS(729669);
-> '1997-10-07'

DATE_FORMAT(date,format)

Formats a date value according to the format string. The following modifiers can be used in the format string: %M month name (January...December)

%W day of the week (Sunday...Saturday)
%D Day of the month with English prefix (1st, 2nd, 3rd, etc.)
%Y Year, number, 4 digits
%y year, number, 2 digits
%a Abbreviated day of the week (Sun...Sat)
%d Day of the month, number (00...31)
%e Day of the month, number (0...31)
%m Month, number (01...12)
%c Month, number (1...12)
%b abbreviated month name (Jan...Dec)
%j Day of the year (001...366)
%H Hour (00...23)
%k hour (0...23)
%h hour (01...12)
%I Hour (01...12)
%l hours (1...12)
%i Minute, 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 Day of the week (0=Sunday...6=Saturday)
%U Week (0...52), where Sunday is the first day of the week
%u Week (0...52), where Monday is the first day of the week
%% A literal "%"

All other characters are copied to the result without interpretation.

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 97 Sat 04 10 Oct 277'
mysql> select DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'

As of MySQL 3.23, % is required before the format modifier character. In earlier versions 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 format modifiers that handle hours, minutes, and seconds. The 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 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 numeric context.

mysql> select CURTIME();
-> '23:50:26'
mysql> select CURTIME() + 0;
-> 235026

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 numeric context.

mysql> select NOW();
-> '1997-12-15 23:50:26'
mysql> select NOW() + 0;
-> 19971215235026

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
If called with no arguments, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT). If UNIX_TIMESTAMP() is called with a date argument, it returns the number of seconds since '1970-01-01 00:00:00' GMT. date can be a DATE string, a DATETIME string, a TIMESTAMP, or a number representing local time in the format YYMMDD or YYYYMMDD.

mysql> select UNIX_TIMESTAMP();
-> 882226357
mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580

When UNIX_TIMESTAMP is used with a TIMESTAMP column, the function accepts the value directly, with no implicit "string-to-unix-timestamp" conversion.

FROM_UNIXTIME(unix_timestamp)

Returns the value represented by the unix_timestamp argument in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or 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 timestamp, formatted according to the format string. format may contain the same modifiers as listed for the DATE_FORMAT() function.

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 argument converted to hours, minutes, and seconds. The value is formatted as 'HH:MM:SS' or HHMMSS, depending on whether the function is used in a string or 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 time parameter, converted to seconds.

mysql> select TIME_TO_SEC('22:23:00');
-> 80580
mysql> select TIME_TO_SEC('00:39:38');
-> 2378

MySQL has many date functions. The function used for date comparison is not necessarily a single function. It may be a combination of multiple functions. So you have to use your imagination.