Examples of common date and calculation functions in MySQL

Date comparison and calculation in MYSQL database are often used, such as comparing the size of two dates and calculating the number of days between two dates. This article mainly shares with you examples of commonly used date and calculation functions in MySQL, hoping to help everyone. .

unix_timestamp()

The unix_timestamp function can accept one parameter or no parameters.

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

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

With this function, time comparison can be naturally converted into a comparison of unsigned integers.

For example, determine whether a time is within an interval

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

Here is an example of using the date function.

The following query selects all records whose 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 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('1997-10-04 22:23:00');
-> 5
mysql> select WEEKDAY('1997-11-05');
-> 2

DAYOFMONTH(date)

Returns the day of the month for date, in the range of 1 to 31.

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

DAYOFYEAR(date)

Returns the number of days in the year for date, 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 day of 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 the quarter of the year in date, ranging from 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 the date, in the range 0 to 52. The 2-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday. If the second parameter is 0, the week starts on Sunday, if the second parameter is 1, it 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, 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 minute of time, ranging from 0 to 59.

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

SECOND(time)

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

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

PERIOD_ADD(P,N)

Add N months to phase P (in format YYMM or YYYYMM). Returns the 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 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)

The execution date of these functions Operation. As of 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 DATETIME or DATE value that specifies the start date, expr is an expression that specifies the interval value to be added to or subtracted from the start date, expr is a string; it may start with a "-" Represents a negative interval. type is a keyword that specifies how the expression should be interpreted. The EXTRACT(type FROM date) function returns the "type" interval from a date. The following table shows how the type and expr parameters are related: type value meaning expected expr format

SECOND seconds SECONDS

MINUTE minutes MINUTES
HOUR time HOURS
DAY days DAYS
MONTH Month MONTHS
YEAR YearYEARS
MINUTE_SECOND Minutes and seconds"MINUTES:SECONDS"
HOUR_MINUTE Hours and minutes"HOURS:MINUTES"
DAY_HOUR Days and hours"DAYS HOURS"
YEAR_MONTH Years and months "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在expr格式中允许任何标点分隔符。表示显示的是建议的分隔符。如果date参数是一个DATE值并且你的计算仅仅包含YEAR、MONTH和DAY部分(即，没有时间部分)，结果是一个DATE值。否则结果是一个DATETIME值.

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

如果你指定太短的间隔值(不包括type关键词期望的间隔部分)，MySQL假设你省掉了间隔值的最左面部分。例如，

如果你指定一个type是DAY_SECOND，值expr被希望有天、小时、分钟和秒部分。如果你象"1:10"这样指定值，

MySQL假设日子和小时部分是丢失的并且值代表分钟和秒。换句话说，"1:10" DAY_SECOND以它等价于"1:10"

MINUTE_SECOND 的方式解释，这对那MySQL解释TIME值表示经过的时间而非作为一天的时间的方式有二义性。如果你使用确实不正确的日期，

结果是NULL。如果你增加MONTH、YEAR_MONTH或YEAR并且结果日期大于新月份的最大值天数，日子在新月用最大的天调整。

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

注意，从前面的例子中词INTERVAL和type关键词不是区分大小写的。

TO_DAYS(date)

给出一个日期date，返回一个天数(从0年的天数)。

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

FROM_DAYS(N)

给出一个天数N，返回一个DATE值。

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

DATE_FORMAT(date,format)

根据format字符串格式化date值。下列修饰符可以被用在format字符串中： %M 月名字(January……December)

%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%"

所有的其他字符不做解释被复制到结果中。

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'

MySQL3.23中，在格式修饰符字符前需要%。在MySQL更早的版本中，%是可选的。

TIME_FORMAT(time,format)

这象上面的DATE_FORMAT()函数一样使用，但是format字符串只能包含处理小时、分钟和秒的那些格式修饰符。其他修饰符产生一个NULL值或0。

CURDATE()

CURRENT_DATE以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符串还是数字上下文被使用。

mysql> select CURDATE();
-> '1997-12-15'
mysql> select CURDATE() + 0;
-> 19971215

CURTIME()

CURRENT_TIME

以'HH:MM:SS'或HHMMSS格式返回当前时间值，取决于函数是在一个字符串还是在数字的上下文被使用。

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

NOW()

SYSDATE()

CURRENT_TIMESTAMP 以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回当前的日期和时间，取决于函数是在一个字符串还是在数字的上下文被使用。

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

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
如果没有参数调用，返回一个Unix时间戳记(从'1970-01-01 00:00:00'GMT开始的秒数)。如果UNIX_TIMESTAMP()用一 个date参数被调用，它返回从'1970-01-01 00:00:00' GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME 字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。

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

当UNIX_TIMESTAMP被用于一个TIMESTAMP列，函数将直接接受值，没有隐含的“string-to-unix-timestamp”变换。

FROM_UNIXTIME(unix_timestamp)

以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回unix_timestamp参数所表示的值，取决于函数是在一个字符串
还是或数字上下文中被使用。

mysql> select FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> select FROM_UNIXTIME(875996580) + 0;
-> 19971004222300

FROM_UNIXTIME(unix_timestamp,format)

返回表示 Unix 时间标记的一个字符串，根据format字符串格式化。format可以包含与DATE_FORMAT()函数列出的条目同样的修饰符。

mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(),
'%Y %D %M %h:%i:%s %x');
-> '1997 23rd December 03:43:30 x'

SEC_TO_TIME(seconds)

返回seconds参数，变换成小时、分钟和秒，值以'HH:MM:SS'或HHMMSS格式化，取决于函数是在一个字符串还是在数字 上下文中被使用。

mysql> select SEC_TO_TIME(2378);
-> '00:39:38'
mysql> select SEC_TO_TIME(2378) + 0;
-> 3938

TIME_TO_SEC(time)

返回time参数，转换成秒。

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

Mysql的日期函数很多，用作日期比较的函数有时并不一定是一个函数有可能是多个函数组合在一起使用，所以大家要发挥自己的想像力

相关推荐：

mysql常用日期时间/数值函数_MySQL

MySQL日期函数详解

php 及MYSQL日期比较方法详解

The above is the detailed content of Examples of common date and calculation functions in MySQL. For more information, please follow other related articles on the PHP Chinese website!