| Function | Description | Example |
|---|---|---|
| ADDDATE(d,n) | Adds time value (interval) n to date d. | SELECT ADDDATE(‘2022-06-10’, INTERVAL 5 DAY); ->2022-06-15 |
| ADDTIME(t,n) | Adds time n to time t. | SELECT ADDTIME(‘2022-06-10 10:00:00’,5); ->2022-06-10 10:00:05 |
| CURDATE() CURRENT_DATE() |
Returns the current date. | SELECT CURDATE(); ->2022-06-10 |
| CURRENT_TIME CURTIME() |
Returns the current time. | SELECT CURRENT_TIME(); ->17:10:31 |
| CURRENT_TIMESTAMP() LOCALTIME() LOCALTIMESTAMP() NOW() SYSDATE() |
Returns the current date and time. | SELECT CURRENT_TIMESTAMP(); ->2022-06-10 17:11:06 |
| DATE() | Extracts the date part of a date or datetime expression. | SELECT DATE(‘2022-06-10’); ->2022-06-10 |
| DATEDIFF(d1,d2) | Subtracts two dates. d1 and d2 each specify a date. | SELECT DATEDIFF(‘2022-06-10’,‘2021-06-10’); ->365 |
| DATE_ADD(d,INTERVAL expr type) | Add time values (intervals) to date d. type can be set to SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, and YEAR. |
SELECT DATE_ADD(‘2022-06-10 17:17:21’, INTERVAL -3 HOUR); ->2022-06-10 14:17:21 |
| DATE_FORMAT(d,f) | Formats date d based on expression f. | SELECT DATE_FORMAT(‘2022-06-10 17:21:11’,’%Y-%m-%d %r’); ->2022-06-10 05:21:11 PM |
| DATE_SUB(date,INTERVAL expr type) | Subtracts a time value (interval) from date date. type can be set to SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, and YEAR. |
SELECT DATE_SUB( CURRENT_DATE(),INTERVAL 2 DAY); ->2022-06-08 |
| DAY(d) | Returns the day in date d. | SELECT DAY(‘2022-06-10’); ->10 |
| DAYNAME(d) | Returns the name of the weekday from date** **d, for example, Monday. | SELECT DAYNAME(‘2022-06-10 17:30:30’); ->Friday |
| DAYOFMONTH(d) | Returns the day of the month from date d. | SELECT DAYOFMONTH(‘2022-06-10 17:31:11’); ->10 |
| DAYOFWEEK(d) | Returns the weekday index from date d. The return value ranges from 1 to 7 and value 1 indicates Sunday. |
SELECT DAYOFWEEK(‘2022-06-10 17:35:11’); ->6 |
| DAYOFYEAR(d) | Returns the day of the year from date d. | SELECT DAYOFYEAR(‘2022-06-10 18:02:11’); ->161 |
| EXTRACT(type FROM d) | Extracts part of date d. type can be set to SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, and YEAR. |
SELECT EXTRACT(MONTH FROM ‘2022-06-10 18:02:33’) ; ->6 |
| HOUR(t) | Extracts the hour from time t. | SELECT HOUR(‘18:06:31’); ->18 |
| LAST_DAY(d) | Returns the last day of the month from date d. | SELECT LAST_DAY(“2022-06-10”); ->2022-06-30 |
| MAKEDATE(year, day-of-year) | Creates a date based on the given year and day of year. year specifies the year. day-of-year specifies the day of year. |
SELECT MAKEDATE(2022,161); ->2022-06-10 |
| MAKETIME(hour, minute, second) | Creates time based on the given hour, minute, and second. | SELECT MAKETIME(11,35,4); ->11:35:04 |
| MICROSECOND(date) | Returns the microseconds from date date. | SELECT MICROSECOND(‘2022-06-10 18:12:00.000023’); ->23 |
| MINUTE(t) | Returns the minute from time t. | SELECT MINUTE(‘18:12:31’); ->12 |
| MONTHNAME(d) | Returns the name of the month from date d, such as November. | SELECT MONTHNAME(‘2022-06-10 18:13:19’); ->June |
| MONTH(d) | Returns the month from date d. The return value ranges from 1 to 12. |
SELECT MONTH(‘2022-06-10 18:14:11’); ->6 |
| PERIOD_ADD(period, number) | Adds a period (expressed in months) to a year-month. period specifies the year-month. number specifies the period to add. |
SELECT PERIOD_ADD(202206,5); ->202211 |
| PERIOD_DIFF(period1, period2) | Returns the number of months between periods. | SELECT PERIOD_DIFF(202204,202012); ->16 |
| QUARTER(d) | Returns the quarter from date d. The return value ranges from 1 to 4. |
SELECT QUARTER(‘2022-06-10 18:16:29’); ->2 |
| SECOND(t) | Returns the second from time t. | SELECT SECOND(‘18:17:36’); ->36 |
| SEC_TO_TIME(s) | Converts time s which is expressed in seconds to the hh:mm:ss format. | SELECT SEC_TO_TIME(4320); ->01:12:00 |
| STR_TO_DATE(string, format_mask) | Converts a string to a date. | SELECT STR_TO_DATE(‘June 10 2022’,’%M %d %Y’); ->2022-06-10 |
| SUBDATE(d,n) | Subtracts interval n from date d. | SELECT SUBDATE(‘2022-06-10 18:19:27’,15); ->2022-05-26 18:19:27 |
| SUBTIME(t,n) | Subtracts period_ n_ from time t. n is expressed in seconds. | SELECT SUBTIME(‘2022-06-10 18:21:11’,5); ->2022-06-10 18:21:06 |
| TIME(expression) | Extracts the time portion of an expression. | SELECT TIME(‘18:22:10’); ->18:22:10 |
| TIME_FORMAT(t,f) | Formats time t based on expression f. | SELECT TIME_FORMAT(‘18:22:59’,’%r’); ->06:22:59 PM |
| TIME_TO_SEC(t) | Converts time t to seconds. | SELECT TIME_TO_SEC(‘18:24:00’); ->66240 |
| TIMEDIFF(time1, time2) | Subtracts two points in time. time1 and time2 each specify a point in time. |
SELECT TIMEDIFF(‘18:24:11’,‘13:10:10’); ->05:14:01 |
| TIMESTAMP(expression, interval) | With a single argument, this function returns the date or datetime expression. With two arguments, this function returns the sum of the arguments. | SELECT TIMESTAMP(‘2022-06-10’, ‘18:25:17’); ->2022-06-10 18:25:17 |
| TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) | Subtracts two datetime expressions. datetime_expr1 and datetime_expr2 each specify a datetime expression. unit specifies the unit of the return value. |
1. SELECT TIMESTAMPDIFF(DAY,‘2020-12-23’,‘2022-04-02’); ->465 2. SELECT TIMESTAMPDIFF(MONTH,‘2020-12-23’,‘2022-04-02’); ->15 |
| TO_DAYS(d) | Converts date d to the number of days since date 0000-01-01. | SELECT TO_DAYS(‘2022-06-10 00:00:00’); ->738681 |
| WEEK(d) | Returns the week number of date_ d_. The return value ranges from 0 to 53. |
SELECT WEEK(‘2022-06-10 00:00:00’); ->23 |
| WEEKDAY(d) | Returns the weekday index of date d. For example, return value 0 indicates Monday and 1 indicates Tuesday. |
SELECT WEEKDAY(‘2022-06-10’); ->4 |
| WEEKOFYEAR(d) | Returns the calendar week of date d. The return value ranges from 0 to 53. |
SELECT WEEKOFYEAR(‘2022-06-10 11:11:11’); ->23 |
| YEAR(d) | Returns the year of date d. | SELECT YEAR(‘2022-06-10’); ->2022 |
| YEARWEEK(date, mode) | Returns the year and week number (value range: 0 to 53). mode is optional and specifies what day a week starts on. For example, return value 0 indicates Sunday and 1 indicates Monday. |
SELECT YEARWEEK(‘2022-06-10’); ->202223 |
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
2025年4月中国数据库流行度排行榜：OB高分复登顶，崖山稳驭撼十强
墨天轮编辑部
2470次阅读
2025-04-09 15:33:27
数据库国产化替代深化：DBA的机遇与挑战
代晓磊
1141次阅读
2025-04-27 16:53:22
2025年3月国产数据库中标情况一览：TDSQL大单622万、GaussDB大单581万……
通讯员
820次阅读
2025-04-10 15:35:48
2025年4月国产数据库中标情况一览：4个千万元级项目，GaussDB与OceanBase大放异彩！
通讯员
648次阅读
2025-04-30 15:24:06
数据库，没有关税却有壁垒
多明戈教你玩狼人杀
568次阅读
2025-04-11 09:38:42
天津市政府数据库框采结果公布，7家数据库产品入选！
通讯员
549次阅读
2025-04-10 12:32:35
国产数据库需要扩大场景覆盖面才能在竞争中更有优势
白鳝的洞穴
530次阅读
2025-04-14 09:40:20
【专家有话说第六期】数据库考证到底有用么？国产时代DBA如何构建真实竞争力
墨天轮编辑部
483次阅读
2025-05-06 17:50:06
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
456次阅读
2025-04-17 17:02:24
一页概览：Oracle GoldenGate
甲骨文云技术
453次阅读
2025-04-30 12:17:56
