十三、MySQL基础系列笔记之日期和时间函数
常见的日期和时间函数有：
<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">select</span> curdate(),curtime(),now(),unix_timestamp(now()),from_unixtime(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1602860664</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+------------+-----------+---------------------+-----------------------+---------------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| curdate() | curtime() | now() | unix_timestamp(now()) | from_unixtime(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1602860664</span>) |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+------------+-----------+---------------------+-----------------------+---------------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2020</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">10</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">16</span> | <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">23</span>:08:07 | <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2020</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">10</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">16</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">23</span>:08:07 | <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1602860887</span> | <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2020</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">10</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">16</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">23</span>:04:24 |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+------------+-----------+---------------------+-----------------------+---------------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">select</span> hour(curtime()),minute(curtime()),monthname(now()),week(now()),<span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">year</span>(now()),hour(curtime());</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+-----------------+-------------------+------------------+-------------+-------------+-----------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| hour(curtime()) | minute(curtime()) | monthname(now()) | week(now()) | <span style="box-sizing: border-box;color: rgb(232, 191, 106) !important">year</span>(now()) | hour(curtime()) |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+-----------------+-------------------+------------------+-------------+-------------+-----------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">23</span> | <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">7</span> | October | <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">41</span> | <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2020</span> | <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">23</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+-----------------+-------------------+------------------+-------------+-------------+-----------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>
date_format(date,fmt)格式化时间
date_format格式化的格式有许多，功能也非常强大，格式符如下：
date_add(date,interval expr type)-返回与所给日期date相差interval时间段的日期
其中intervel是间隔类型关键字，expr是一个表达式，这个表达式对应后面的类型，type是间隔类型。间隔类型如下：
<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(98, 151, 85) !important"># 第一列，返回当前时间，第二列，当前时间距离31天后的日期时间，第三列：返回距离当前时间一年2个月后日期时间</span></span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">select</span> now(),date_add(now(),<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">interval</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">31</span> day),date_add(now(),<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">interval</span> <span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'1_2'</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">year_month</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+---------------------+---------------------------------+-------------------------------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| now() | date_add(now(),<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">interval</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">31</span> day) | date_add(now(),<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">interval</span> <span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'1_2'</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">year_month</span>) |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+---------------------+---------------------------------+-------------------------------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2020</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">10</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">16</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">23</span>:41:03 | <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2020</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">11</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">16</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">23</span>:41:03 | <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2021</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">12</span>-<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">16</span> <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">23</span>:41:03 |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+---------------------+---------------------------------+-------------------------------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>
我是小白，期待和优秀的你一起同行!
小白
2020年10月16日
请登录后再评论