mysql中日期函数之间的联系与区别

最新推荐文章于 2024-03-25 20:55:49 发布

CodeMartain

最新推荐文章于 2024-03-25 20:55:49 发布

阅读量357

点赞数

分类专栏： Database_All 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_54061333/article/details/119997613

版权

Database_All 专栏收录该内容

62 篇文章 1 订阅

订阅专栏

日期函数

日期函数

日期函数

当前日期函数

当前日期的函数
curdate()与current_date() 作用一样
curdate()+0是将时间转化为数字;


mysql> select curdate() as date1,current_date() as date2,curdate()+0 as date3;
+------------+------------+----------+
| date1      | date2      | date3    |
+------------+------------+----------+
| 2021-08-30 | 2021-08-30 | 20210830 |
+------------+------------+----------+
1 row in set (0.00 sec)

当前时间函数

当前时间的函数


mysql> select curtime() as time1,current_time(),curtime()+0 as time2;
+----------+----------------+--------+
| time1    | current_time() | time2  |
+----------+----------------+--------+
| 15:17:14 | 15:17:14       | 151714 |
+----------+----------------+--------+
1 row in set (0.00 sec)

当前日期和时间函数


mysql> select current_timestamp() as time1,localtime(),now(),sysdate() as time2;
+---------------------+---------------------+---------------------+---------------------+
| time1               | localtime()         | now()               | time2               |
+---------------------+---------------------+---------------------+---------------------+
| 2021-08-30 15:22:59 | 2021-08-30 15:22:59 | 2021-08-30 15:22:59 | 2021-08-30 15:22:59 |
+---------------------+---------------------+---------------------+---------------------+

时间戳函数

既然是时间戳至少需要包含日期,—以返回‘1970-01-0100:00:00’GMT到现在的秒数

mysql> select unix_timestamp(),unix_timestamp(now()),unix_timestamp(current_time);
+------------------+-----------------------+------------------------------+
| unix_timestamp() | unix_timestamp(now()) | unix_timestamp(current_time) |
+------------------+-----------------------+------------------------------+
|       1630308333 |            1630308333 |                   1630308333 |
+------------------+-----------------------+------------------------------+
1 row in set (0.00 sec)

UNIX_TIMESTAMP(date)若无参数调用，则返回一个UNIX时间戳（‘1970-01-01
00:00:00’GMT之后的秒数）作为无符号整数。其中，GMT（Green wich mean
time）为格林尼治标准时间。若用date来调用UNIX_TIMESTAMP()，它会将参数值以‘1970-01-01 00:00:00’GMT后的秒数的形式返回。date可以是一个DATE字符串、DATETIME字符串、TIMESTAMP或一个当地时间的YYMMDD或YYYYMMDD格式的数字。

时间戳函数的反函数

既然时间可以转换为时间戳,也可以反过来—from_unixtime(数字);


mysql> select from_unixtime(1630308333);
+---------------------------+
| from_unixtime(1630308333) |
+---------------------------+
| 2021-08-30 15:25:33       |
+---------------------------+
1 row in set (0.00 sec)

标准时间和日期函数

UTC_TIME()----当前时区的时间
UTC_DATE()--------------返回当前时区日期的时间


mysql> select UTC_TIME(),UTC_DATE(),UTC_TIME()+0,UTC_DATE()+0;
+------------+------------+--------------+--------------+
| UTC_TIME() | UTC_DATE() | UTC_TIME()+0 | UTC_DATE()+0 |
+------------+------------+--------------+--------------+
| 07:33:02   | 2021-08-30 |        73302 |     20210830 |
+------------+------------+--------------+--------------+
1 row in set (0.00 sec)

月份函数和获取月份名称函数

mysql> select month(1000),monthname(1000);
+-------------+-----------------+
| month(1000) | monthname(1000) |
+-------------+-----------------+
|          10 | October         |
+-------------+-----------------+
1 row in set (0.00 sec)

mysql> select month(now()),monthname(now());
+--------------+------------------+
| month(now()) | monthname(now()) |
+--------------+------------------+
|            8 | August           |
+--------------+------------------+
1 row in set (0.00 sec)


mysql> select month(20190304),monthname('2019,05,01');
+-----------------+-------------------------+
| month(20190304) | monthname('2019,05,01') |
+-----------------+-------------------------+
|               3 | May                     |
+-----------------+-------------------------+
1 row in set (0.00 sec)

获取星期几的函数

DAYNAME(d)----返回当前日期为周几的名称
DAYOFWEEK(d)-----返回当前为一周的第几天,(周日算第一天)
WEEKDAY(d)------返回当前为一周第几天,周一为第一天;


mysql> select dayname(20210809)as t1,dayofweek(20210809),weekday(20211019);
+--------+---------------------+-------------------+
| t1     | dayofweek(20210809) | weekday(20211019) |
+--------+---------------------+-------------------+
| Monday |                   2 |                 1 |
+--------+---------------------+-------------------+
1 row in set (0.00 sec)

获取星期数的函数

week(20210809),表示返回当前日期为一年中的第几周,默认星期日为一周第一天;
week(20210809,1),表示返回当前日期为一年中的第几周,指定周一为一周开始;

weekofyear(20210809),返回当前日期为一年中的第几周;

mysql> select week(20210809),week(20210809,1),weekofyear(20210809),weekofyear(20210809);
+----------------+------------------+----------------------+----------------------+
| week(20210809) | week(20210809,1) | weekofyear(20210809) | weekofyear(20210809) |
+----------------+------------------+----------------------+----------------------+
|             32 |               32 |                   32 |                   32 |
+----------------+------------------+----------------------+----------------------+
1 row in set (0.00 sec)

获取天数的函数

天数主要是是为了获得一年中的第几天,一个月中的第几天,一周的第几天

mysql> select dayofyear(20210909),dayofmonth(20210909),dayofweek(20100909);
+---------------------+----------------------+---------------------+
| dayofyear(20210909) | dayofmonth(20210909) | dayofweek(20100909) |
+---------------------+----------------------+---------------------+
|                 252 |                    9 |                   5 |
+---------------------+----------------------+---------------------+

获取年、季度、小时、分钟和秒钟的函数

之前提到了获取月份,日期,还可以单独获取其他时间的函数

mysql> select year(20220909),quarter(20210909),hour(now()),minute(20210909122334),second(now());
+----------------+-------------------+-------------+------------------------+---------------+
| year(20220909) | quarter(20210909) | hour(now()) | minute(20210909122334) | second(now()) |
+----------------+-------------------+-------------+------------------------+---------------+
|           2022 |                 3 |          15 |                     23 |             7 |
+----------------+-------------------+-------------+------------------------+---------------+
1 row in set (0.00 sec)

也非常的见名知意;

从时间中获取需要的信息

mysql> SELECT EXTRACT(YEAR FROM '2018-07-02' ) AS coll,
    -> EXTRACT(YEAR_MONTH FROM '2018-07-12 01:02:03') AS col2,
    ->  EXTRACT(DAY_MINUTE FROM '2018-07-12 01:02:03') AS col3;
+------+--------+--------+
| coll | col2   | col3   |
+------+--------+--------+
| 2018 | 201807 | 120102 |
+------+--------+--------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(YEAR FROM now() ) AS coll,
    -> EXTRACT(YEAR_MONTH FROM now()) AS col2,
    ->  EXTRACT(DAY_MINUTE FROM now()) AS col3;
+------+--------+------+
| coll | col2   | col3 |
+------+--------+------+
| 2021 | 202108 | 1642 |
+------+--------+------+
1 row in set (0.00 sec)

时间和秒钟转换

TIME_TO_SEC(time)返回已转化为秒的time参数,
转换公式为：小时3600+分钟60+秒。

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

mysql> select sec_to_time(61403);
+--------------------+
| sec_to_time(61403) |
+--------------------+
| 17:03:23           |
+--------------------+
1 row in set (0.00 sec)

计算日期和时间的函数

计算日期和时间的函数有DATE_ADD()、ADDDATE()、DATE_SUB()、SUBDATE()、ADDTIME()、SUBTIME()和DATE_DIFF()。

在这里插入图片描述
date_add(时间,interval 要增加的值增加的时间类型)
adddate(时间,interval 要增加的值增加的时间类型)


mysql> select date_add(now(),interval 1 year)as  time1,adddate(current_date(),interval 2 month) as time2;
+---------------------+------------+
| time1               | time2      |
+---------------------+------------+
| 2022-08-30 17:10:11 | 2021-10-30 |
+---------------------+------------+
1 row in set (0.00 sec)

有加就有减------
date_sub(时间,interval 要减少的值减少的时间类型)
subdate(时间,interval 要减少的值增加的时间类型)

mysql> select date_sub(curdate() ,interval 1 month)as col1,subdate(now() ,interval 1 month) as col2;
+------------+---------------------+
| col1       | col2                |
+------------+---------------------+
| 2021-07-30 | 2021-07-30 17:14:55 |
+------------+---------------------+
1 row in set (0.00 sec)

mysql> select date_sub(curdate() ,interval 1 month)as col1,subdate(now() ,interval 1 hour) as col2;
+------------+---------------------+
| col1       | col2                |
+------------+---------------------+
| 2021-07-30 | 2021-08-30 16:15:08 |
+------------+---------------------+
1 row in set (0.00 sec)

加一个负值等于减,减一个负值等于加----基本操作

date类型的操作可以操作到年\月\日\时间

时间加减操作-----

mysql> select time_add(now(),'12:12:12') as col,addtime(now(),'00:00:00')as col2;
ERROR 1305 (42000): FUNCTION test.time_add does not exist
mysql> select addtime(now(),'00:00:00')as col2;
+---------------------+
| col2                |
+---------------------+
| 2021-08-30 17:22:14 |
+---------------------+
1 row in set (0.00 sec)

mysql> select sub_time(now(),'00:38:00') as co1;
ERROR 1305 (42000): FUNCTION test.sub_time does not exist
mysql> select subtime(now(),'00:38:00') as co1;
+---------------------+
| co1                 |
+---------------------+
| 2021-08-30 16:45:11 |
+---------------------+
1 row in set (0.00 sec)

本来以为按照date的逻辑能有time_add,竟然没有…好尴尬!!!

返回两天之间的间隔天数

datediff(date1,date2)返回 date1-date2的间隔天数;

mysql> select datediff('2019-09-09 12:12:12',now())as cltw;
+------+
| cltw |
+------+
| -721 |
+------+
1 row in set (0.00 sec)

日期和时间格式化
DATE_FORMAT(date,format)根据format指定的格式显示date值。
在这里插入图片描述

这个玩意跟java日期转换方法类似-----,选定日期,指定格式

mysql>  SELECT DATE_FORMAT(now(), '%W %M %Y') AS coll,
    -> date_format(now() ,'%D %y %a %d %m %b %j') as coll2;
+--------------------+---------------------------+
| coll               | coll2                     |
+--------------------+---------------------------+
| Monday August 2021 | 30th 21 Mon 30 08 Aug 242 |
+--------------------+---------------------------+
1 row in set (0.00 sec)


mysql> SELECT DATE_FORMAT(now(), '%H:%i:%s') AS col3,
    -> DATE_FORMAT(now(), '%X %V') AS col4;
+----------+---------+
| col3     | col4    |
+----------+---------+
| 17:37:13 | 2021 35 |
+----------+---------+
1 row in set (0.00 sec)

mysql>

除了日期转换还有时间转换函数

mysql> select time_format('17:23:45','%H %K %H %I %L');
+------------------------------------------+
| time_format('17:23:45','%H %K %H %I %L') |
+------------------------------------------+
| 17 K 17 05 L                             |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select time_format('17:23:45','%H %k %h %I %l');
+------------------------------------------+
| time_format('17:23:45','%H %k %h %I %l') |
+------------------------------------------+
| 17 17 05 05 5                            |
+------------------------------------------+
1 row in set (0.00 sec)

获取时间对应的显示格式----

GET_FORMAT(val_type,
format_type)返回日期时间字符串的显示格式，val_type表示日期数据类型，包括DATE、DATETIME和TIME；format_type表示格式化显示类型，包括EUR、INTERVAL、ISO、JIS、USA。

本笔记的目的是为了方便程序员们查看基础知识,同时也是为了回顾一下之前学过的内容----深入;

CodeMartain

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
mysql中日期函数之间的联系与区别

日期函数日期函数当前日期函数当前时间函数当前日期和时间函数时间戳函数时间戳函数的反函数标准时间和日期函数月份函数和获取月份名称函数获取星期几的函数获取星期数的函数获取天数的函数获取年、季度、小时、分钟和秒钟的函数从时间中获取需要的信息时间和秒钟转换计算日期和时间的函数日期函数当前日期函数当前日期的函数curdate()与current_date() 作用一样curdate()+0是将时间转化为数字;mysql> select curdate() as date1,current_date
复制链接

扫一扫