MySQL 中的日期时间

最新推荐文章于 2024-03-06 15:54:29 发布
最新推荐文章于 2024-03-06 15:54:29 发布
阅读量522 收藏
点赞数 1
分类专栏： MySQL 文章标签： mysql 日期时间函数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lamp_yang_3533/article/details/53738061
版权

关于日期时间的比较

标准的 SQL 中，严格的 DATE 格式为 ‘YYYY-MM-DD’ ，如 ‘2018-05-06’ 。

为了方便，如果日期时间是在数值环境下使用的，MySQL 会自动将日期时间转换为数值（反之亦然）。

MySQL 相当智能，在与 TIMESTAMP、DATE 或 DATETIME 列进行比较的 WHERE 子句中，允许【宽松形式】的日期时间字符串。

宽松形式是指，任何标点字符都能用作各部分之间的分隔符。例如，’2016-08-15’ 和 ‘2016#08#15’ 是等同的。MySQL 还能转换不含任何分隔符的字符串（如 ‘20160815’），前提是它必须是有意义的日期时间。

使用 <、<=、=、>=、>、或 BETWEEN 等操作符，将 DATE、TIME、DATETIME 或 TIMESTAMP 与常量字符串进行比较时，MySQL 通常会自动将字符串转换为内部长整数，以便进行快速比较。

SELECT * FROM tbl_name WHERE date >= '2016-05-05';

但是，对于 IN 和 STRCMP，会执行字符串比较。

常用的日期时间函数

now

返回当前的日期和时间。

MariaDB [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2016-11-20 21:46:56 |
+---------------------+

curdate

返回当前的日期。curdate()是current_date()函数的缩写形式。

MariaDB [test]> select curdate();
+------------+
| curdate()  |
+------------+
| 2016-11-20 |
+------------+
1 row in set (0.00 sec)

MariaDB [test]> select current_date();
+----------------+
| current_date() |
+----------------+
| 2016-11-20     |
+----------------+

curtime

返回当前的时间，curtime()是current_time()函数的缩写形式。

MariaDB [test]> select curtime();
+-----------+
| curtime() |
+-----------+
| 21:51:55  |
+-----------+

unix_timestamp

返回指定时间的时间戳，如果参数为空，默认是当前时间。

MariaDB [test]> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1479650714 |
+------------------+
1 row in set (0.00 sec)

MariaDB [test]> select unix_timestamp('2016-11-20');
+------------------------------+
| unix_timestamp('2016-11-20') |
+------------------------------+
|                   1479571200 |
+------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select unix_timestamp('2016-11-20 21:56:30');
+---------------------------------------+
| unix_timestamp('2016-11-20 21:56:30') |
+---------------------------------------+
|                            1479650190 |
+---------------------------------------+

from_unixtime

返回指定时间戳的时间格式。

MariaDB [test]> select from_unixtime(1479650190);
+---------------------------+
| from_unixtime(1479650190) |
+---------------------------+
| 2016-11-20 21:56:30       |
+---------------------------+

year

返回指定时间的年份。

MariaDB [test]> select year('2016-11-20');
+--------------------+
| year('2016-11-20') |
+--------------------+
|               2016 |
+--------------------+
1 row in set (0.00 sec)

MariaDB [test]> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2016 |
+-------------+

datediff

返回两个日期的相差天数。

MariaDB [test]> select datediff(now(), '2016-11-18');
+-------------------------------+
| datediff(now(), '2016-11-18') |
+-------------------------------+
|                             2 |
+-------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select datediff(now(), '2016-11-18 21:00:00');
+----------------------------------------+
| datediff(now(), '2016-11-18 21:00:00') |
+----------------------------------------+
|                                      2 |
+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select datediff(curdate(), '2016-11-25');
+-----------------------------------+
| datediff(curdate(), '2016-11-25') |
+-----------------------------------+
|                                -5 |
+-----------------------------------+

to_days

将日期格式的时间转换为以天为单位的时间，以便进行天数的比较。

MariaDB [test]> select to_days('0000-01-01');
+-----------------------+
| to_days('0000-01-01') |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [test]> select to_days(curdate());
+--------------------+
| to_days(curdate()) |
+--------------------+
|             736653 |
+--------------------+
1 row in set (0.00 sec)

MariaDB [test]> select to_days(curdate()) - to_days('0000-01-01');
+--------------------------------------------+
| to_days(curdate()) - to_days('0000-01-01') |
+--------------------------------------------+
|                                     736652 |
+--------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select to_days(curdate()) - to_days('2016-11-18');
+--------------------------------------------+
| to_days(curdate()) - to_days('2016-11-18') |
+--------------------------------------------+
|                                          2 |
+--------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select to_days(curdate()) - to_days('2016-11-25');
+--------------------------------------------+
| to_days(curdate()) - to_days('2016-11-25') |
+--------------------------------------------+
|                                         -5 |
+--------------------------------------------+

date_format

按照指定的格式，对日期时间字符串进行格式化。

常用的格式说明符有以下几种：

  • %Y： 年份（4位数字）
  • %m： 月份（2位数字）
  • %d： 该月的哪一天（2位数字）
  • %H： 24小时制的小时（2位数字）
  • %i： 分钟（2位数字）
  • %s： 秒数（2位数字）
MariaDB [test]> select date_format(now(), '%Y-%m');
+-----------------------------+
| date_format(now(), '%Y-%m') |
+-----------------------------+
| 2017-12                     |
+-----------------------------+

time_format

按照指定的格式，对时间（时分秒）字符串进行格式化。

mysql> select time_format('09:30', '%H:%i:00');
+----------------------------------+
| time_format('09:30', '%H:%i:00') |
+----------------------------------+
| 09:30:00                         |
+----------------------------------+

time_to_sec

将时间字符串转化为秒。

mysql> select time_to_sec('00:30');
+----------------------+
| time_to_sec('00:30') |
+----------------------+
|                 1800 |
+----------------------+

date_add

给日期时间字符串一个增量，增量的类型（type）可以是以下类型：

  • YEAR
  • MONTH
  • DAY
  • WEEK
  • HOUR
  • MINUTE
  • SECOND
  • MICROSECOND
  • QUARTER
  • YEAR_MONTH
  • DAY_HOUR
  • DAY_MINUTE
  • DAY_SECOND
  • DAY_MICROSECOND
  • HOUR_MINUTE
  • HOUR_SECOND
  • HOUR_MICROSECOND
  • MINUTE_SECOND
  • MINUTE_MICROSECOND
  • SECOND_MICROSECOND

说明：MySQL 中的关键字不区分大小写，但对于关键字，还是推荐使用大写。

mysql> select DATE_ADD('2016-05-05', INTERVAL 2 DAY);
+----------------------------------------+
| DATE_ADD('2016-05-05', INTERVAL 2 DAY) |
+----------------------------------------+
| 2016-05-07                             |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select DATE_ADD('2016-05-05 08:30:30', INTERVAL 2 DAY);
+-------------------------------------------------+
| DATE_ADD('2016-05-05 08:30:30', INTERVAL 2 DAY) |
+-------------------------------------------------+
| 2016-05-07 08:30:30                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select DATE_ADD('2016-05-05 08:30:30', INTERVAL 8 HOUR);
+--------------------------------------------------+
| DATE_ADD('2016-05-05 08:30:30', INTERVAL 8 HOUR) |
+--------------------------------------------------+
| 2016-05-05 16:30:30                              |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_ADD('2016-07-15 08:30:30', INTERVAL '2 4' YEAR_MONTH)  AS res;
+---------------------+
| res                 |
+---------------------+
| 2018-11-15 08:30:30 |
+---------------------+

mysql> SELECT DATE_ADD('2016-07-15 08:30:30', INTERVAL '02:10:15' HOUR_SECOND)  AS res;
+---------------------+
| res                 |
+---------------------+
| 2016-07-15 10:40:45 |
+---------------------+

date_sub

给日期时间字符串一个减量。

mysql> SELECT DATE_SUB('2016-07-15 08:30:30', INTERVAL 2 WEEK);
+--------------------------------------------------+
| DATE_SUB('2016-07-15 08:30:30', INTERVAL 2 WEEK) |
+--------------------------------------------------+
| 2016-07-01 08:30:30                              |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB('2016-07-15 08:30:30', INTERVAL '1 1:1:1' DAY_SECOND);
+----------------------------------------------------------------+
| DATE_SUB('2016-07-15 08:30:30', INTERVAL '1 1:1:1' DAY_SECOND) |
+----------------------------------------------------------------+
| 2016-07-14 07:29:29                                            |
+----------------------------------------------------------------+

date

仅获取目标字符串中的日期（年月日）。

mysql> SELECT DATE(DATE_SUB('2016-07-15 08:30:30', INTERVAL 2 WEEK));
+--------------------------------------------------------+
| DATE(DATE_SUB('2016-07-15 08:30:30', INTERVAL 2 WEEK)) |
+--------------------------------------------------------+
| 2016-07-01                                             |
+--------------------------------------------------------+

time

仅获取目标字符串中的时间（时分秒）。

mysql> SELECT TIME('2016-07-15 08:30:30');
+-----------------------------+
| TIME('2016-07-15 08:30:30') |
+-----------------------------+
| 08:30:30                    |
+-----------------------------+
1 row in set (0.01 sec)

mysql> SELECT TIME('2016-07-15 08:30:30.251');
+---------------------------------+
| TIME('2016-07-15 08:30:30.251') |
+---------------------------------+
| 08:30:30.251000                 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TIME_FORMAT(TIME('2016-07-15 08:30:30.251'), '%H:%i:%s');
+----------------------------------------------------------+
| TIME_FORMAT(TIME('2016-07-15 08:30:30.251'), '%H:%i:%s') |
+----------------------------------------------------------+
| 08:30:30                                                 |
+----------------------------------------------------------+

timestampdiff

计算两个日期时间的时间差。

mysql> SELECT TIMESTAMPDIFF(YEAR,'2015-03-20','2015-05-01');
+-----------------------------------------------+
| TIMESTAMPDIFF(YEAR,'2015-03-20','2015-05-01') |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+

mysql> SELECT TIMESTAMPDIFF(MONTH,'2015-03-20','2015-05-01');
+------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2015-03-20','2015-05-01') |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+

mysql> SELECT TIMESTAMPDIFF(MINUTE,'2015-03-20 08:00:00','2015-03-21 08:01:00');
+-------------------------------------------------------------------+
| TIMESTAMPDIFF(MINUTE,'2015-03-20 08:00:00','2015-03-21 08:01:00') |
+-------------------------------------------------------------------+
|                                                              1441 |
+-------------------------------------------------------------------+
lamp_yang_3533
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql 日期和时间函数
09-11
里是一个使用日期函数的例子。下面的查询选择所有 date_col 值在最后 30 天内的记录。
MySQL常用日期时间函数
weixin_30535843的博客
04-20 891
日期和时间函数 可能的需求： 　　当前时间是多少、下个月的今天是星期几、统计截止到当前日期前 3 天的收入总和…… 上述需求就需要使用日期和时间函数来实现： MySQL服务器中的三种时区设置： 　　①系统时区---保存在系统变量system_time_zone 　　②服务器时区---保存在全局系统变量global.time_zone 　　③每个客户端连接的时区---保存在会话变量...
mysql 日期时间
qq_20610631的博客
01-26 999
搜索最新的时间 select * from po order by fdate desc   今天 select * from 表名 where to_days(时间字段名) = to_days(now()); 昨天 Select * FROM 表名 Where TO_DAYS( NOW( ) ) – TO_DAYS( 时间字段名) &lt;= 1 7天前 如果是7天后就是DATE_...
MySQL 日期操作 详解
最新发布
lizhengyu891231的博客
03-06 522
MySQL 日期操作 详解
MySQL 日期时间
weixin_50160957的博客
06-17 852
一、Mysql 三个常用的时间函数 now (); 当前具体的日期和时间。 可以搭配新增使用 也可以使用其他的 , 在新增的时候给数据添加一个当前具体的日期和时间。 例如: INSERT INTO presentime(gem_time) VALUES ( now () ) 。 curdate (); 当前日期。 可以搭配新增使用 也可以使用其他的 , 在新增的时候给数据添加一个当前日期。 例如: INSERT INTO presentime(gem_time) VALUES ( curdate ()
Mysql中的日期和时间
输微
03-09 3213
MySQL 使用下列数据类型在数据库中存储日期或日期/时间值： • DATE - 格式 YYYY-MM-DD • DATETIME - 格式: YYYY-MM-DD HH:MM:SS • TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS • YEAR - 格式 YYYY 或 YY date用curdate()获取当前时间 而datetime 和 timestamp都用 now...
mysql如何查询日期与时间
12-14
MySQL支持的日期和时间类型有 DATETIME、TIMESTAMP、DATE、TIME、YEAR ，几种类型比较如下： 涉及到日期和时间字段类型选择时，根据存储需求选择合适的类型即可。 2.日期和时间相关函数 处理日期和时间字段的函数...
MySQL 日期时间函数常用总结
01-21
获得当前日期+时间（date + time）1.1 函数：now()  相关函数：current_timestamp()，localtime()，localtimestamp()  举例说明：  2. 获得当前日期（date）  函数：curdate()  相关函数：current_date()...
mysql的日期和时间函数
09-11
mysql的日期和时间函数 这里是一个使用日期函数的例子。
mysql时间日期函数总结
关注软件研发
08-06 1249
 这里是一个使用日期函数的例子。下面的查询选择了所有记录，其date_col的值是在最后30天以内：  mysql> SELECT something FROM table  WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) 30;  DAYOFWEEK(date)  返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标
Mysql —日期和时间函数
深圳市多克创新科技有限公司
06-19 1133
Mysql —日期和时间函数
MySQL日期时间函数大全
jiang_zhongping的博客
11-02 227
DAYOFWEEK(date) 　返回日期date是星期几(1=星期天,2=星期一,……7=星期六,ODBC标准)mysql> select DAYOFWEEK('1998-02-03'); 　　-> 3 WEEKDAY(date) 　返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。 mysql> select WEEKDAY('1997-10-04 22:23:00');
mysql时间日期类型_MySQL之日期时间类型
weixin_27027459的博客
01-19 1070
mysql(5.5)所支持的日期时间类型有：DATETIME、TIMESTAMP、DATE、TIME、YEAR。几种类型比较如下：日期时间类型占用空间日期格式最小值最大值零值表示DATETIME8 bytesYYYY-MM-DD HH:MM:SS1000-01-01 00:00:009999-12-31 23:59:590000-00-00 00:00:00TIMESTAMP4 bytesYYY...
mysql日期时间类型
corbin
01-08 9071
DATETIME类型： 以YYYY-MM-DD HH:MM:SS[.fraction]格式存储日期时间 datetime =YYYY-MM-DD HH:MM:SS格式不存储微秒  datetime(6) =YYYY-MM-DD HH:MM:SS.fraction则会存储微妙 DATETIME类型与时区无关，占用8个字节的存续空间，时间范围是1000-01-01 00：00：00到9999-...
MySQL 日期与时间函数
hupei
06-12 906
返回从0000年到现在的天数。将时间/日期间隔添加到日期。从日期减去指定的时间间隔。
mysql的日期时间类型及精度问题/time、date、datetime、timestamp和year
weixin_49114503的博客
06-08 1万+
二、 mysql中的日期时间类型 2.1、详细解释 2.2、占用字节 2.3、time类型 三、datetime和timestamp区别 3.1、相同点 3.2、不同点 四、mysql日期类型中的坑 4.1、 time、timestamp、datetime数据类型四舍五入 五、其他疑问问题 5.1、date类型可以存时分秒么？可以存储不存在的日期么？
Mysql的日期和时间类型
qq_38661220的博客
09-20 232
Mysql的日期和时间类型 记录：在本周工作中，有一个需求要将某个字段的默认带值给去除，在去除之后运行时又出现了insert错误的情况，开始以为原因是该数据库字段为DATETIME类型，必须要有默认值，没有默认值时就会出现插入错误的情况，后改为timestamp类型解决。但是后来整理博客和测试之后发现DATETIME类型没有默认值也是可以插入的，怀疑可能与开发环境版本有关？ mysql与java的对应关系 MySQL Type Name Return value of GetColumnClass
MYSQL数据库时间字段INT,TIMESTAMP,DATETIME性能效率比较
热门推荐
adsadadaddadasda的博客
12-29 2万+
正 文：    在数据库设计的时候，我们经常会需要设计时间字段，在MYSQL中，时间字段可以使用int、timestamp、datetime三种类型来存储，那么这三种类型哪一种用来存储时间性能比较高，效率好呢？飘易就这个问题，来一个实践出真知吧。 MYSQL版本号：5.5.19 建立表： CREATE TABLE IF N
mysql 更改日期时间格式
07-28
要更改 MySQL 中日期时间的格式，您可以使用日期和时间函数以及格式化函数来转换日期时间值的显示格式。下面是一些常用的函数和示例： 1. DATE_FORMAT() 函数：将日期时间值按照指定的格式进行格式化。 例如，将日期时间字段 `my_datetime` 的格式从默认的 YYYY-MM-DD HH:MM:SS 更改为 DD/MM/YYYY HH:MM AM/PM： ```sql SELECT DATE_FORMAT(my_datetime, '%d/%m/%Y %h:%i %p') AS formatted_datetime FROM my_table; ``` 2. STR_TO_DATE() 函数：将字符串转换为日期时间值，使用指定的格式。 例如，将字符串 '2022-01-01' 转换为日期值： ```sql SELECT STR_TO_DATE('2022-01-01', '%Y-%m-%d') AS converted_date; ``` 3. CONCAT() 函数：将日期和时间字段的不同部分连接在一起。 例如，将日期字段 `my_date` 和时间字段 `my_time` 连接为一个完整的日期时间值： ```sql SELECT CONCAT(my_date, ' ', my_time) AS full_datetime FROM my_table; ``` 这些只是一些示例，您可以根据需要使用不同的函数和格式来更改日期时间的显示方式。请注意，这些函数只会影响查询结果的显示格式，并不会更改存储在数据库中的实际值。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值