mysql 日期相关知识点及函数

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

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

如果你将一个DATE值赋给一个DATETIME或TIMESTAMP对象,结果值的时间部分被设置为'00:00:00',因为DATE值不包含时间信息
如果你将一个DATETIME或TIMESTAMP值赋给一个DATE对象,结果值的时间部分被删除,因为DATE类型不存储时间信息
例如:
 
  1. mysql> desc datetime_val; 
  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> desc timestamp_val; 
  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> select * from timestamp_val; 
  20. Empty set (0.00 sec) 
  21. mysql> insert into timestamp_val(select * from datetime_val); 
  22. Query OK, 4 rows affected, 1 warning (0.00 sec) 
  23. Records: 4  Duplicates: 0  Warnings: 1 
  24. mysql> select * from timestamp_val; 
  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日期数据类型之间的转换
 
  1. 在mysql中实现日期数据类型之间的转换的方式很多, 
  2. 比如采用简单的left(),right(),mid(),substring(),substring_index(),concat函数等都可以实现转换,但是每种函数都有使用的局限性-->上述函数都是字符函数。 
  3.   
  4. 这里介绍一下日期函数的简单使用 
  5. DATE_FORMAT() ,TIME_FORMAT(),STR_TO_DATE() 三个函数都接受格式化串作为参数。 
  6. DATE_FORMAT(date_col,'format') 
  7. date_col :为日期格式的列数据类型 
  8. format:格式化字符 
  9. %Y:  年份  数字形式 4位数 
  10. %y:  年份  数字形式 2位数 
  11. %M:   完整的月份名称(january-December) 
  12. %m:     月份 数字形式(01..12) 
  13. %b:     月份名称的前三个字母 
  14. %c:     月份,数字形式(1..12) 
  15. %r      时间,12小时制 以AM或PM结尾 
  16. %T      时间,24小时制 
  17.   
  18. 如果date_format,time_format,str_to_date() 三个函数不能按照用户的需求转换时,需要使用存储过程或者函数实现。 
  19. to_days()和from_days()函数可以再一个日期值和对应的天数之间进行转化。 
  20. to_days()函数将一个日期值转化为对应的天数 
  21. from_days() 则执行相反的过程。 
  22. 例如:  
  1. mysql> select dt,to_days(dt) from datetime_val; 
  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> select dt, from_days(to_days(dt)) from datetime_val; 
  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> select dt,unix_timestamp(dt) from datetime_val; 
  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> select dt,unix_timestamp(dt),from_unixtime(unix_timestamp(dt)) from datetime_val; 
  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) 

 

  1. 三 :计算两个时间之间的间隔
  2.  
  3. mysql提供的函数有 datediff,timediff,timstampdiff 函数
  4. 下面逐一介绍
  • DATEDIFF()函数 
  1. 可以作用于date-and-date或者date-and-time类型得数据,但是在处理过程中将忽略时间部分值,也就是说datediff()函数可以用于计算 
  2. 两个date ,datetime,timestamp类型值之间间隔的天数。 
  3. 格式:datadiff(date1,date2) 表示date1-date2之间的天数、 
  4.  
  • TIMEDIFF()函数 
  1. 可以计算两个time类型值之间的时间间隔 
  2. 介绍的参数可以是time类型或者date-time类型   注:要求两个参数类型相同 
  3.  
  4. 格式timediff(time1,time2) 表示time1-time2之间的时间间隔 
  5.  
  • timestampdiff()函数 
  1. 格式:timestampdiff(unit,val_1,val_2) 
  2. unit: year,month,day,hour,minute,second,frac_second等时间单位 
  3. 例如 
  4. mysql> set @t1='2020-12-12 14:14:14',@t2='2012-11-11 14:50:50'
  5. Query OK, 0 rows affected (0.00 sec) 
  6.  
  7. mysql> select timestampdiff(year,@t2,@t1)as year,timestampdiff(month,@t2,@t1)as month,timestampdiff(day,@t2,@t1)as days; 
  8. +------+-------+------+ 
  9. | year | month | days | 
  10. +------+-------+------+ 
  11. |    8 |    97 | 2952 |  
  12. +------+-------+------+ 
  13. 1 row in set (0.00 sec) 
  14.  
  15. 注意:如果val_1 大于val_2是返回的是负数,这与datediff(),timediff()中的参数顺序与返回值的关系相反。 
  16.       参数的有效范围与函数名无关,并不接受timestamp类型范围限制,可以超出timestamp类型的取值范围。 
  17.       mysql5.0以后的版本才支持该函数。 
  1. 四 :时间加减函数 
  2. mysql提供 
  3. addtime(t1,t2)函数 
  4. timestamp()函数 
  5. 例如 
  6. mysql> set @d1='1997-07-01 12:00:00' ,@t='12:00:00'
  7.         Query OK, 0 rows affected (0.00 sec) 
  8.  
  9. mysql> select timestamp(@d1,@t); 
  10. +---------------------+ 
  11. | timestamp(@d1,@t)   | 
  12. +---------------------+ 
  13. | 1997-07-02 00:00:00 |  
  14. +---------------------+ 
  15. 1 row in set (0.01 sec) 
  16.   
  17. mysql还提供了date_add()和date_sub()函数实现日期值和以一个时间值进行加法或减法运算 
  18. 语法 
  19. date_add(date,interval val unit)    加法   
  20. date_sub(date,interval val unit)    减法 
  21. date:为日期类型的值或者列 
  22. 例如: 
  23. mysql> select * from date_val; 
  24. +------+------------+ 
  25. | id   | d          | 
  26. +------+------------+ 
  27. |    1 | 1864-02-28 |  
  28. |    2 | 1900-01-15 |  
  29. |    3 | 1987-03-05 |  
  30. |    4 | 1999-12-31 |  
  31. |    5 | 2000-06-04 |  
  32. +------+------------+ 
  33. 5 rows in set (0.00 sec) 
  34.  
  35. mysql> select d,date_add(d,interval 7 day) from date_val; 
  36. +------------+----------------------------+ 
  37. | d          | date_add(d,interval 7 day) | 
  38. +------------+----------------------------+ 
  39. | 1864-02-28 | 1864-03-06                 |  
  40. | 1900-01-15 | 1900-01-22                 |  
  41. | 1987-03-05 | 1987-03-12                 |  
  42. | 1999-12-31 | 2000-01-07                 |  
  43. | 2000-06-04 | 2000-06-11                 |  
  44. +------------+----------------------------+ 
  45. 5 rows in set (0.00 sec) 
  46.  
  47. mysql> select d,date_sub(d,interval 7 day) from date_val; 
  48. +------------+----------------------------+ 
  49. | d          | date_sub(d,interval 7 day) | 
  50. +------------+----------------------------+ 
  51. | 1864-02-28 | 1864-02-21                 |  
  52. | 1900-01-15 | 1900-01-08                 |  
  53. | 1987-03-05 | 1987-02-26                 |  
  54. | 1999-12-31 | 1999-12-24                 |  
  55. | 2000-06-04 | 2000-05-28                 |  
  56. +------------+----------------------------+ 
  57. 5 rows in set (0.00 sec) 
  58. 注意:在mysql5.0以后的版本中,可以使用1 week 代替7 day,但是计算结果将是一个datetime类型,而不再是date类型
  59. 另外unti还可以这样子写如:hour_minute,day_hour等等
  60. 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 |
  61. 五:总结--OVER
  62. mysql提供了丰富的日期函数,这里不再一一列举了,他们使用起来很强大。基础的东西需要好好掌握,欢迎指正。