mysql日期处理函数,Mysql钦赐日期区间的领到方式

在数据库搬砖的经过中,免不了要跟日期打交道,比方按日期汇总一些指标、总结某段时间内的总的数量等。

mysql 日期时间管理函数,mysql日期管理函数

  • ADDDATE(date,interval expr unit卡塔尔,ADDDATE(expr,daysState of Qatar —
    增添时光值(时区)为日期值,其为DATE_金沙8331网址,ADD()的别名

DATA_ADD(date,INTERVAL expr unit)、DATE_SUB(date,INTERVAL expr unit)

这几个函数举行时间运算;date参数钦定初叶日期的日期或日期时间值,expr是一个表明式钦命要抬高或调整和减弱的值的间隔的起来日期

unit Value Expected expr Format
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROSECOND 'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND 'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND 'MINUTES:SECONDS'
HOUR_MICROSECOND 'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND 'HOURS:MINUTES:SECONDS'
HOUR_MINUTE 'HOURS:MINUTES'
DAY_MICROSECOND 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE 'DAYS HOURS:MINUTES'
DAY_HOUR 'DAYS HOURS'
YEAR_MONTH 'YEARS-MONTHS'

 

也足以编写

date + INTERVAL expr unit
date - INTERVAL expr unit

 

mysql> select '2008-1-31' + interval 1 month;
+--------------------------------+
| '2008-1-31' + interval 1 month |
+--------------------------------+
| 2008-02-29                     |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2008-1-31',interval 1 month);
+----------------------------------------+
| date_add('2008-1-31',interval 1 month) |
+----------------------------------------+
| 2008-02-29                             |
+----------------------------------------+
1 row in set (0.00 sec)

 

获得一天的始发和尾声的时刻

mysql> select '2015-4-4 0:0:0' + interval 1 day - interval 1 second;
+-------------------------------------------------------+
| '2015-4-4 0:0:0' + interval 1 day - interval 1 second |
+-------------------------------------------------------+
| 2015-04-04 23:59:59                                   |
+-------------------------------------------------------+
1 row in set (0.00 sec)

 

 

  • ADDTIME(expr1,expr2卡塔尔(قطر‎ — 增添expr2到expr1上并回到结果;expr1
    是贰个年华或许日期时间表明式,expr2为三个岁月表明式

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
        -> '2008-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
        -> '03:00:01.999997'
  • CONVERT_TZ() Convert from one timezone to another
  • CUWranglerDATE(State of Qatar — 重返当前的日期 YYYY-MM-DD,

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2015-04-03 |
+------------+
1 row in set (0.00 sec)

mysql> select curdate() + 0;
+---------------+
| curdate() + 0 |
+---------------+
|      20150403 |
+---------------+
1 row in set (0.00 sec)

 

  • CURRENT_DATE(), CURRENT_DATE  —- CURDATE() 的别名
  • CURRENT_TIME(), CURRENT_TIME — CURTIME()的别名
  • CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP — NOW()的别名
  • CURTIME(State of Qatar — 重回当前的时日

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 14:06:30  |
+-----------+
1 row in set (0.00 sec)

mysql> select curtime() + 0;
+---------------+
| curtime() + 0 |
+---------------+
| 140641.000000 |
+---------------+
1 row in set (0.00 sec)

 

  • DATE_ADD() Add time values (intervals) to a date value
  • DATE_FORMAT(date,format卡塔尔(قطر‎ 依据格式化字符串 格式化日期

格式字符串中能够动用 ‘%’ 表明符

常用的:

  %a — 星期几的缩写  (Sun..Sat卡塔尔(قطر‎

  %b — 月份的缩写 (Jan…Dec卡塔尔(قطر‎

  %c — 月份 (0–12)

  %D — 天数 加上了爱尔兰语后缀 (0th,1sh…卡塔尔

  %d,%e —  月份中的天数 (0-31卡塔尔国

  %H —  24小时制 (00- 23)

  %h,%I — 12小时制 (00-12)  

  %i — 分钟 (00-59)

  %M — 月份 (january,december)

  %m — 月份 (00-12)

  %S,%s — 秒 (00-59)

  %Y — 年 4位

  %y — 年 2位

mysql> select date_format(now(),'%Y/%m/%d %H:%m:%s');
+----------------------------------------+
| date_format(now(),'%Y/%m/%d %H:%m:%s') |
+----------------------------------------+
| 2015/04/03 14:04:05                    |
+----------------------------------------+
1 row in set (0.00 sec)

 

mysql日期处理函数,Mysql钦赐日期区间的领到方式。 

  • DATE_SUB() Subtract a time value (interval) from a date
  • DATE(卡塔尔国 — 提取日期部分

mysql> select date(now());
+-----------------+
| date(curdate()) |
+-----------------+
| 2015-04-03      |
+-----------------+
1 row in set (0.00 sec)

 

  • DATEDIFF(expr1,expr2卡塔尔(قطر‎ 重返expr1 –
    expr2以内的时刻差;expr1,expr2表明式独有日期部分参加运算

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+
1 row in set (0.00 sec)

 

  • DAY() Synonym for DAYOFMONTH()
  • DAYNAME() Return the name of the weekday
  • DAYOFMONTH() Return the day of the month (0-31)
  • DAYOFWEEK() Return the weekday index of the argument
  • DAYOFYEAR() Return the day of the year (1-366)
  • EXTRACT() Extract part of a date
  • FROM_DAYS() Convert a day number to a date
  • FROM_UNIXTIME(unix_timestamp[,format]State of Qatar将unix时间戳格式化为日期字符串

mysql> select from_unixtime(unix_timestamp(),'%Y-%m-%d');
+--------------------------------------------+
| from_unixtime(unix_timestamp(),'%Y-%m-%d') |
+--------------------------------------------+
| 2015-04-03                                 |
+--------------------------------------------+
1 row in set (0.00 sec)

 

  • GET_FORMAT() Return a date format string
  • HOU智跑(time卡塔尔(قطر‎ — 提取时辰有些

mysql> select HOUR(now());
+-------------+
| HOUR(now()) |
+-------------+
|          14 |
+-------------+
1 row in set (0.00 sec)

 

  • LAST_DAY(date)–
    再次来到钦命月份的末段一天。如若参数无效,则赶回NULL。

mysql> select last_day('2013-2-5');
+----------------------+
| last_day('2013-2-5') |
+----------------------+
| 2013-02-28           |
+----------------------+
1 row in set (0.00 sec)

mysql> select last_day('2004-2-5');
+----------------------+
| last_day('2004-2-5') |
+----------------------+
| 2004-02-29           |
+----------------------+
1 row in set (0.00 sec)

mysql> select last_day('2004-2-35');
+-----------------------+
| last_day('2004-2-35') |
+-----------------------+
| NULL                  |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

 

  • LOCALTIME(), LOCALTIME Synonym for NOW()
  • LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW()
  • MAKEDATE(year,dayofyear) Create a date from the year and day of year
  • MAKETIME MAKETIME(hour,minute,second)
  • MICROSECOND(exprState of Qatar — 再次来到expr表达式的皮秒数

  • MINUTE() Return the minute from the argument
  • MONTH() Return the month from the date passed
  • MONTHNAME(date卡塔尔国 — 重回月份的全称

mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| April            |
+------------------+
1 row in set (0.00 sec)

 

  • NOW(卡塔尔国 — 重回当前的日期和岁月
  • PERIOD_ADD() Add a period to a year-month
  • PERIOD_DIFF() Return the number of months between periods
  • QUARTER() Return the quarter from a date argument
  • SEC_TO_TIME() Converts seconds to ‘HH:MM:SS’ format
  • SECOND() –重回秒数 (0-59State of Qatar
  • STR_TO_DATE() Convert a string to a date
  • SUBDATE() A synonym for DATE_SUB() when invoked with three
    arguments
  • SUBTIME() Subtract times
  • SYSDATE() Return the time at which the function executes
  • TIME_FORMAT() Format as time
  • TIME_TO_SEC() Return the argument converted to seconds
  • TIME() Extract the time portion of the expression passed
  • TIMEDIFF() Subtract time
  • TIMESTAMP() With a single argument, this function returns the date
    or datetime expression; with two arguments, the sum of the arguments
  • TIMESTAMPADD() Add an interval to a datetime expression
  • TIMESTAMPDIFF() Subtract an interval from a datetime expression
  • TO_DAYS() Return the date argument converted to days
  • TO_SECONDS() Return the date or datetime argument converted to
    seconds since Year 0
  • UNIX_TIMESTAMP() — 返回unix时间戳

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1428042298 |
+------------------+
1 row in set (0.00 sec)

 

  • UTC_DATE() Return the current UTC date
  • UTC_TIME() Return the current UTC time
  • UTC_TIMESTAMP() Return the current UTC date and time
  • WEEK() Return the week number
  • WEEKDAY() Return the weekday index
  • WEEKOFYEAR() Return the calendar week of the date (0-53)
  • YEA奥迪Q5(卡塔尔国 — 获取年份

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2015 |
+-------------+
1 row in set (0.00 sec)

 

  • YEARWEEK() Return the year and week

日期时间管理函数,mysql日期管理函数
ADDDATE(date,interval expr unit卡塔尔(قطر‎,ADDDATE(expr,days卡塔尔(قطر‎ —
增添岁月值(时区)为日期值,其为DATE_ADD()的别名…

若果是定位的日子辛亏,只需直接钦定就能够,但众多时候都以索要基于当下日期自适应变化的。比方:提取前一周生机勃勃到那周天的数码、提取下月的数码、提取前N个月的数额。。。

这几个供给都有七个协同点,正是要视当今日期而定!那么,大家就供给首先取安妥今日期的有的信息,如当前几天子是本周第几天、前段时间第几天等,然后,技巧做下一步管理。

一、在领取所需日期区间早前,大家先介绍多少个常用的函数

-- 先运行这一句SET @t = '2018-07-26 11:41:29';-- 再运行这一句SELECT DATE(@t)当前日期,YEAR(@t) 年份, MONTH(@t)月份,WEEK(@t)本年第几周, DAY(@t) 本月第几天, HOUR(@t)小时, MINUTE(@t)分钟, SECOND(@t)秒

运维出来,结果是那样的:

二、上面介绍多少个常用的日子加减运算函数

1、ADDDATE(expr, days) / SUBDATE(expr, days):

ADDDAT函数有四个形参,第三个是规范日期,也即须要运算的日子;第二个是一个间距表明式,形如:INTE福特ExplorerVAL
1 HOUTiggo,此中INTE揽胜VAL
是间距的乐趣,中间的数字1方可换来自由整数,第多个hour能够替换来day/month/year等时间单位。

SUBDATE同理,只不过变成了减法运算。

生机勃勃体化的用法如下:

SELECT ADDDATE('2018-07-26 11:41:29',INTERVAL 1 HOUR);SELECT SUBDATE('2018-07-26 11:41:29',INTERVAL 1 HOUR);

2、DATE_ADD() / DATE_SUB():

用法同ADDDATE(expr, days) / SUBDATE(expr, days)。

三、日期区间截取

接下去,利用方面介绍的日期函数的用法,就足以开展日期区间的截取了。

1、上周

-- 提取上周的日期区间SELECT CURDATE() NOW,ADDDATE(ADDDATE(DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 1 DAY),-6),0) startdate,ADDDATE(DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 1 DAY),0) duetodate

2、上月

-- 上月SELECT CURDATE() NOW, DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY), INTERVAL -1 MONTH) startdate,DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE()) DAY), INTERVAL 0 MONTH) duetodate

3、前五个月

-- 前四个月SELECT CURDATE() NOW, ADDDATE(ADDDATE(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY), INTERVAL -4 MONTH) startdate,DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE()) DAY), INTERVAL 0 MONTH) duetodate

4、下星期二到本星期四

一时不是根据自然周进行总括的,须求自定义七日的起止日期,可以据守上边包车型地铁法子:

SELECT ADDDATE(DATE_SUB(CURDATE(),INTERVAL (IF(WEEKDAY(CURDATE())IN(3,4,5,6),WEEKDAY(CURDATE()),WEEKDAY(CURDATE())+7 )) + 1 DAY),-3) startdate, ADDDATE(DATE_SUB(CURDATE(),INTERVAL (IF(WEEKDAY(CURDATE())IN(3,4,5,6),WEEKDAY(CURDATE()),WEEKDAY(CURDATE())+7 )) + 1 DAY),3)duetodate

OK,至此就足以兑现时间、日期的新闻提取、猖獗区间的截取了~

总结

如上所述是我给我们介绍的Mysql内定日期区间的领取方法,希望对我们全数助于,假若我们有别的疑问请给自家留言,笔者会及时回复大家的。在这里也极其多谢大家对台本之家网址的支撑!

发表评论

电子邮件地址不会被公开。 必填项已用*标注