Mysql 日期时间函数

Mysql 日期时间函数

• datetime和timestamp区别：
• timestamp类型字段特殊性：
• Mysql获取日期时间函数：
  — now()
  —curdate()
  —curtime()
  —Extract()
  —last_day()
• Mysql日期时间计算函数:
  —date_add()
  —date_sub()
  —datediff(),timediff()
  —str_to_date()
  —date_format(),time_format()
• MySQL 时区（timezone）转换函数：
  datetime和timestamp区别：

1.存储方式不一样，IMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。
2.占用存储空间不同。timestamp储存占用4个字节，datetime储存占用8个字节
3.可表示的时间范围不同。timestamp可表示范围:1970-01-01 00:00:00~2038-01-09 03:14:07，datetime支持的范围更宽1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
4.TIMESTAMP类型在默认情况下，insert、update 数据时，TIMESTAMP列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。
5.索引速度不同。timestamp更轻量，索引相对datetime更快。

Mysql获取日期时间函数：

now()

sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。
mysql> select now(), sleep(3), now();

±--------------------±---------±--------------------+
| now() | sleep(3) | now() |
±--------------------±---------±--------------------+
| 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 |
±--------------------±---------±--------------------+

mysql> select sysdate(), sleep(3), sysdate();

±--------------------±---------±--------------------+
| sysdate() | sleep(3) | sysdate() |
±--------------------±---------±--------------------+
| 2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 |
±--------------------±---------±--------------------+

可以看到，虽然中途 sleep 3 秒，但 now() 函数两次的时间值是相同的； sysdate() 函数两次得到的时间值相差 3 秒.

curdate()
select curdate();

±-----------+
| curdate() |
±-----------+
| 2019-09-16 |
±-----------+
下面两个函数等同于curdate();
current_date() ,current_date

select current_time();

±-----------+
| current_time() |
±-----------+
| 11:43:21 |
±-----------+

Extract()

选取日期时间的各个部分：日期，时间，年，季度，月，日，小时，分钟，秒，微妙

set @dt='2019-09-26 11:22:33.123456';
select date(@dt);-- 2019-09-26
select time(@dt);-- 11:22:33.123456
select year(@dt);-- 2019
select quarter(@dt); -- 3
select month(@dt); -- 9
select week(@dt); -- 38
select day(@dt); -- 26
select hour(@dt); -- 11
select second(@dt); -- 33
select microsecond(@dt); -- 123456

Extract() 函数，可以上面实现类似的功能

select extract(year from @dt);-- 2019
select extract(quarter from @dt);-- 3
select extract(year_month from @dt);-- 201909

last_day()

last_day() 函数：返回月份中的最后一天。
select last_day(‘2008-02-01’); – 2008-02-29
select last_day(‘2008-08-08’); – 2008-08-31

MySQL last_day() 函数非常有用，比如我想得到当前月份中有多少天，可以这样来计算

select now(),day(last_day(now()));

±--------------------±-----+
| now() | days |
±--------------------±-----+
| 2019-09-26 12:04:32| 30 |
±--------------------±-----+

Mysql 日期时间计算函数：

date_add()

set @dt=now();
select date_add(@dt,interval 1 day); -- 加一天
select date_add(now(),interval 1 day);-- 加一天
select date_add(@dt,interval 1 hour);-- 加 1小时
select date_add(@dt,interval 1 week); -- 
select date_add(@dt,interval 1 second);
select date_add(@dt,interval -1 day);-- = sub
select date_sub(@dt,interval 1 day);

MySQL adddate(), addtime()函数，可以用 date_add() 来替代。

下面是 date_add() 实现 addtime() 功能示例：

 set @dt = '2008-08-09 12:12:33';
 
 select date_add(@dt, interval '01:15:30' hour_second);

+------------------------------------------------+
| date_add(@dt, interval '01:15:30' hour_second) |
+------------------------------------------------+
| 2008-08-09 13:28:03 |
+------------------------------------------------+

 select date_add(@dt, interval '1 01:15:30' day_second);

+-------------------------------------------------+
| date_add(@dt, interval '1 01:15:30' day_second) |
+-------------------------------------------------+
| 2008-08-10 13:28:03 |
+-------------------------------------------------+

date_sub() 日期时间函数 和 date_add() 用法一致，不再赘述

MySQL datediff(date1,date2)：两个日期相减 date1 - date2，返回天数

select datediff('2019-09-10','2019-09-01');--    9
select datediff('2019-09-01','2019-09-10');--   -9

timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值。

select timediff('2019-09-10 11:22:33','2019-09-09 11:20:33')--  24:02:00

str_to_date()

（字符串转换为日期）函数：str_to_date(str, format)

select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09
select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

date_format(),time_format(),将日期，时间转换成字符串

select date_format('2008-08-08 22:23:00', '%W %M %Y');

±-----------------------------------------------+
| date_format(‘2008-08-08 22:23:00’, ‘%W %M %Y’) |
±-----------------------------------------------+
| Friday August 2008 |
±-----------------------------------------------+

select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');

±---------------------------------------------------+
| date_format(‘2008-08-08 22:23:01’, ‘%Y%m%d%H%i%s’) |
±---------------------------------------------------+
| 20080808222301 |
±---------------------------------------------------+

select time_format(‘22:23:01’, ‘%H.%i.%s’);

±------------------------------------+
| time_format(‘22:23:01’, ‘%H.%i.%s’) |
±------------------------------------+
| 22.23.01 |
±------------------------------------+