mysql datetime赋值 mysql给日期赋值空

转载

mob64ca13f9e726 2023-10-23 19:02:21

文章标签 mysql datetime赋值数据库 mysql unix 数据类型 文章分类 MySQL 数据库

mysql 日期相关知识点及函数

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

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

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

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

例如：

1. mysql>
2. +-------+----------+------+-----+---------+----------------+ 
3. | Field | Type     | Null | Key | Default | Extra          | 
4. +-------+----------+------+-----+---------+----------------+ 
5. | id    | int(11)  | NO   | PRI | NULL    | auto_increment |  
6. | dt    | datetime | YES  |     | NULL    |                |  
7. +-------+----------+------+-----+---------+----------------+ 
8. 2 rows in set (0.00 sec) 
9.   
10. mysql>
11. +-------+-----------+------+-----+-------------------+-----------------------------+ 
12. | Field | Type      | Null | Key | Default           | Extra                       | 
13. +-------+-----------+------+-----+-------------------+-----------------------------+ 
14. | id    | int(11)   | NO   | PRI | NULL              | auto_increment              |  
15. | ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |  
16. +-------+-----------+------+-----+-------------------+-----------------------------+ 
17. 2 rows in set (0.00 sec) 
18.   
19. mysql>
20. Empty set (0.00 sec) 
21. mysql>
22. Query OK, 4 rows affected, 1 warning (0.00 sec) 
23. Records: 4  Duplicates: 0  Warnings: 1 
24. mysql>
25. +----+---------------------+ 
26. | id | ts                  | 
27. +----+---------------------+ 
28. |  1 | 0000-00-00 00:00:00 |  
29. |  2 | 1987-03-05 12:30:15 |  
30. |  3 | 1999-12-31 09:00:00 |  
31. |  4 | 2000-06-04 15:45:30 |  
32. +----+---------------------+ 
33. 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() 则执行相反的过程。
例如:

1. mysql>
2. +---------------------+-------------+ 
3. | dt                  | to_days(dt) | 
4. +---------------------+-------------+ 
5. | 1970-01-01 00:00:00 |      719528 |  
6. | 1987-03-05 12:30:15 |      725800 |  
7. | 1999-12-31 09:00:00 |      730484 |  
8. | 2000-06-04 15:45:30 |      730640 |  
9.   
10. mysql>
11. +---------------------+------------------------+ 
12. | dt                  | from_days(to_days(dt)) | 
13. +---------------------+------------------------+ 
14. | 1970-01-01 00:00:00 | 1970-01-01             |  
15. | 1987-03-05 12:30:15 | 1987-03-05             |  
16. | 1999-12-31 09:00:00 | 1999-12-31             |  
17. | 2000-06-04 15:45:30 | 2000-06-04             |  
18. +---------------------+------------------------+ 
19.   
20. 在datetime或者timestamp类型值和秒数之间进行转换 
21.   
22. timestamp类型的取值范围1970年-2037年 
23. 可以使用unix_timestamp()函数和from_unixtime()函数 与从1970年开始的秒数进行转换。 
24. 将一个timestamp或者datetime类型转换为秒数，比转换为天数更为精确 
25. 例如 
26.   
27. mysql>
28. +---------------------+--------------------+ 
29. | dt                  | unix_timestamp(dt) | 
30. +---------------------+--------------------+ 
31. | 1970-01-01 00:00:00 |                  0 |  
32. | 1987-03-05 12:30:15 |          541917015 |  
33. | 1999-12-31 09:00:00 |          946602000 |  
34. | 2000-06-04 15:45:30 |          960104730 |  
35. +---------------------+--------------------+ 
36. 4 rows in set (0.00 sec) 
37.   
38. mysql>
39. +---------------------+--------------------+-----------------------------------+ 
40. | dt                  | unix_timestamp(dt) | from_unixtime(unix_timestamp(dt)) | 
41. +---------------------+--------------------+-----------------------------------+ 
42. | 1970-01-01 00:00:00 |                  0 | 1970-01-01 08:00:00               |  
43. | 1987-03-05 12:30:15 |          541917015 | 1987-03-05 12:30:15               |  
44. | 1999-12-31 09:00:00 |          946602000 | 1999-12-31 09:00:00               |  
45. | 2000-06-04 15:45:30 |          960104730 | 2000-06-04 15:45:30               |  
46. +---------------------+--------------------+-----------------------------------+ 
47. 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>
+------+-------+------+
| year | month | days |
+------+-------+------+
| 8 | 97 | 2952 |
+------+-------+------+
1 row in set (0.00 sec)
注意：如果val_1 大于val_2是返回的是负数，这与datediff()，timediff()中的参数顺序与返回值的关系相反。
参数的有效范围与函数名无关，并不接受timestamp类型范围限制，可以超出timestamp类型的取值范围。
mysql5.0以后的版本才支持该函数。

四：时间加减函数

1. mysql提供 
2. addtime(t1,t2)函数 
3. timestamp()函数 
4. 例如 
5. mysql> set @d1='1997-07-01 12:00:00' ,@t='12:00:00'; 
6.         Query OK, 0 rows affected (0.00 sec) 
7.  
8. mysql>
9. +---------------------+ 
10. | timestamp(@d1,@t)   | 
11. +---------------------+ 
12. | 1997-07-02 00:00:00 |  
13. +---------------------+ 
14. 1 row in set (0.01 sec) 
15.   
16. mysql还提供了date_add()和date_sub()函数实现日期值和以一个时间值进行加法或减法运算 
17. 语法 
18. date_add(date,interval val unit)    加法   
19. date_sub(date,interval val unit)    减法 
20. date：为日期类型的值或者列 
21. 例如： 
22. mysql>
23. +------+------------+ 
24. | id   | d          | 
25. +------+------------+ 
26. |    1 | 1864-02-28 |  
27. |    2 | 1900-01-15 |  
28. |    3 | 1987-03-05 |  
29. |    4 | 1999-12-31 |  
30. |    5 | 2000-06-04 |  
31. +------+------------+ 
32. 5 rows in set (0.00 sec) 
33.  
34. mysql>
35. +------------+----------------------------+ 
36. | d          | date_add(d,interval 7 day) | 
37. +------------+----------------------------+ 
38. | 1864-02-28 | 1864-03-06                 |  
39. | 1900-01-15 | 1900-01-22                 |  
40. | 1987-03-05 | 1987-03-12                 |  
41. | 1999-12-31 | 2000-01-07                 |  
42. | 2000-06-04 | 2000-06-11                 |  
43. +------------+----------------------------+ 
44. 5 rows in set (0.00 sec) 
45.  
46. mysql>
47. +------------+----------------------------+ 
48. | d          | date_sub(d,interval 7 day) | 
49. +------------+----------------------------+ 
50. | 1864-02-28 | 1864-02-21                 |  
51. | 1900-01-15 | 1900-01-08                 |  
52. | 1987-03-05 | 1987-02-26                 |  
53. | 1999-12-31 | 1999-12-24                 |  
54. | 2000-06-04 | 2000-05-28                 |  
55. +------------+----------------------------+ 
56. 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提供了丰富的日期函数，这里不再一一列举了，他们使用起来很强大。基础的东西需要好好掌握，欢迎指正。

转载于:https://blog.51cto.com/haicang/1086625

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。