mysql 日期相关知识点及函数

最新推荐文章于 2024-09-30 17:19:22 发布

weixin_34346099

最新推荐文章于 2024-09-30 17:19:22 发布

阅读量79

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/haicang/1086625

版权

mysql 日期相关知识点及函数

一：mysql中日期数据类型之间的相互赋值导致的影响

在某种程度上，你可以把一种日期类型的值赋给一个不同的日期类型的对象。然而，这可能值有一些改变或信息的损失

如果你将一个DATE值赋给一个DATETIME或TIMESTAMP对象，结果值的时间部分被设置为'00:00:00'，因为DATE值不包含时间信息

如果你将一个DATETIME或TIMESTAMP值赋给一个DATE对象，结果值的时间部分被删除，因为DATE类型不存储时间信息。

例如：

mysql> desc datetime_val;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc timestamp_val;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)
mysql> select * from timestamp_val;
Empty set (0.00 sec)
mysql> insert into timestamp_val(select * from datetime_val);
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 1
mysql> select * from timestamp_val;
+----+---------------------+
| id | ts |
+----+---------------------+
| 1 | 0000-00-00 00:00:00 |
| 2 | 1987-03-05 12:30:15 |
| 3 | 1999-12-31 09:00:00 |
| 4 | 2000-06-04 15:45:30 |
+----+---------------------+
4 rows in set (0.00 sec)

注：在日期数据类型之间进行赋值时，一定要注意可能会损失时间精度。

二：mysql日期数据类型之间的转换

在mysql中实现日期数据类型之间的转换的方式很多，
比如采用简单的left(),right(),mid()，substring（），substring_index()，concat函数等都可以实现转换，但是每种函数都有使用的局限性-->上述函数都是字符函数。
这里介绍一下日期函数的简单使用
DATE_FORMAT() ,TIME_FORMAT(),STR_TO_DATE() 三个函数都接受格式化串作为参数。
DATE_FORMAT(date_col,'format')
date_col :为日期格式的列数据类型
format:格式化字符
%Y: 年份数字形式 4位数
%y: 年份数字形式 2位数
%M: 完整的月份名称（january-December)
%m: 月份数字形式（01..12)
%b: 月份名称的前三个字母
%c: 月份，数字形式（1..12）
%r 时间，12小时制以AM或PM结尾
%T 时间，24小时制
如果date_format,time_format,str_to_date() 三个函数不能按照用户的需求转换时，需要使用存储过程或者函数实现。
to_days()和from_days()函数可以再一个日期值和对应的天数之间进行转化。
to_days()函数将一个日期值转化为对应的天数
from_days() 则执行相反的过程。
例如:

mysql> select dt,to_days(dt) from datetime_val;
+---------------------+-------------+
| dt | to_days(dt) |
+---------------------+-------------+
| 1970-01-01 00:00:00 | 719528 |
| 1987-03-05 12:30:15 | 725800 |
| 1999-12-31 09:00:00 | 730484 |
| 2000-06-04 15:45:30 | 730640 |
mysql> select dt, from_days(to_days(dt)) from datetime_val;
+---------------------+------------------------+
| dt | from_days(to_days(dt)) |
+---------------------+------------------------+
| 1970-01-01 00:00:00 | 1970-01-01 |
| 1987-03-05 12:30:15 | 1987-03-05 |
| 1999-12-31 09:00:00 | 1999-12-31 |
| 2000-06-04 15:45:30 | 2000-06-04 |
+---------------------+------------------------+
在datetime或者timestamp类型值和秒数之间进行转换
timestamp类型的取值范围1970年-2037年
可以使用unix_timestamp()函数和from_unixtime()函数与从1970年开始的秒数进行转换。
将一个timestamp或者datetime类型转换为秒数，比转换为天数更为精确
例如
mysql> select dt,unix_timestamp(dt) from datetime_val;
+---------------------+--------------------+
| dt | unix_timestamp(dt) |
+---------------------+--------------------+
| 1970-01-01 00:00:00 | 0 |
| 1987-03-05 12:30:15 | 541917015 |
| 1999-12-31 09:00:00 | 946602000 |
| 2000-06-04 15:45:30 | 960104730 |
+---------------------+--------------------+
4 rows in set (0.00 sec)
mysql> select dt,unix_timestamp(dt),from_unixtime(unix_timestamp(dt)) from datetime_val;
+---------------------+--------------------+-----------------------------------+
| dt | unix_timestamp(dt) | from_unixtime(unix_timestamp(dt)) |
+---------------------+--------------------+-----------------------------------+
| 1970-01-01 00:00:00 | 0 | 1970-01-01 08:00:00 |
| 1987-03-05 12:30:15 | 541917015 | 1987-03-05 12:30:15 |
| 1999-12-31 09:00:00 | 946602000 | 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 | 960104730 | 2000-06-04 15:45:30 |
+---------------------+--------------------+-----------------------------------+
4 rows in set (0.01 sec)

三：计算两个时间之间的间隔
mysql提供的函数有 datediff,timediff,timstampdiff 函数
下面逐一介绍

DATEDIFF()函数

可以作用于date-and-date或者date-and-time类型得数据，但是在处理过程中将忽略时间部分值，也就是说datediff()函数可以用于计算
两个date ,datetime,timestamp类型值之间间隔的天数。
格式:datadiff(date1,date2) 表示date1-date2之间的天数、

TIMEDIFF()函数

可以计算两个time类型值之间的时间间隔
介绍的参数可以是time类型或者date-time类型注：要求两个参数类型相同
格式timediff(time1，time2）表示time1-time2之间的时间间隔

timestampdiff()函数

格式：timestampdiff(unit,val_1,val_2)
unit: year,month,day,hour,minute,second,frac_second等时间单位
例如
mysql> set @t1='2020-12-12 14:14:14',@t2='2012-11-11 14:50:50';
Query OK, 0 rows affected (0.00 sec)
mysql> select timestampdiff(year,@t2,@t1)as year,timestampdiff(month,@t2,@t1)as month,timestampdiff(day,@t2,@t1)as days;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 8 | 97 | 2952 |
+------+-------+------+
1 row in set (0.00 sec)
注意：如果val_1 大于val_2是返回的是负数，这与datediff()，timediff()中的参数顺序与返回值的关系相反。
参数的有效范围与函数名无关，并不接受timestamp类型范围限制，可以超出timestamp类型的取值范围。
mysql5.0以后的版本才支持该函数。

四：时间加减函数
mysql提供
addtime(t1,t2)函数
timestamp()函数
例如
mysql> set @d1='1997-07-01 12:00:00' ,@t='12:00:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select timestamp(@d1,@t);
+---------------------+
| timestamp(@d1,@t) |
+---------------------+
| 1997-07-02 00:00:00 |
+---------------------+
1 row in set (0.01 sec)
mysql还提供了date_add()和date_sub()函数实现日期值和以一个时间值进行加法或减法运算
语法
date_add(date,interval val unit) 加法
date_sub(date,interval val unit) 减法
date：为日期类型的值或者列
例如：
mysql> select * from date_val;
+------+------------+
| id | d |
+------+------------+
| 1 | 1864-02-28 |
| 2 | 1900-01-15 |
| 3 | 1987-03-05 |
| 4 | 1999-12-31 |
| 5 | 2000-06-04 |
+------+------------+
5 rows in set (0.00 sec)
mysql> select d,date_add(d,interval 7 day) from date_val;
+------------+----------------------------+
| d | date_add(d,interval 7 day) |
+------------+----------------------------+
| 1864-02-28 | 1864-03-06 |
| 1900-01-15 | 1900-01-22 |
| 1987-03-05 | 1987-03-12 |
| 1999-12-31 | 2000-01-07 |
| 2000-06-04 | 2000-06-11 |
+------------+----------------------------+
5 rows in set (0.00 sec)
mysql> select d,date_sub(d,interval 7 day) from date_val;
+------------+----------------------------+
| d | date_sub(d,interval 7 day) |
+------------+----------------------------+
| 1864-02-28 | 1864-02-21 |
| 1900-01-15 | 1900-01-08 |
| 1987-03-05 | 1987-02-26 |
| 1999-12-31 | 1999-12-24 |
| 2000-06-04 | 2000-05-28 |
+------------+----------------------------+
5 rows in set (0.00 sec)
注意：在mysql5.0以后的版本中，可以使用1 week 代替7 day,但是计算结果将是一个datetime类型，而不再是date类型
另外unti还可以这样子写如：hour_minute,day_hour等等
mysql> select d,date_add(d,interval '3 4' day_hour) from date_val; +------------+-------------------------------------+ | d | date_add(d,interval '3 4' day_hour) | +------------+-------------------------------------+ | 1864-02-28 | 1864-03-02 04:00:00 | | 1900-01-15 | 1900-01-18 04:00:00 | | 1987-03-05 | 1987-03-08 04:00:00 | | 1999-12-31 | 2000-01-03 04:00:00 | | 2000-06-04 | 2000-06-07 04:00:00 |
五：总结--OVER
mysql提供了丰富的日期函数，这里不再一一列举了，他们使用起来很强大。基础的东西需要好好掌握，欢迎指正。