mysql里面关于时间的数据类型，一共有5种，DATE, TIME, DATETIME, TIMESTAMP, 和YEAR。

date

只保存日期，范围从1000-01-01到9999-12-31。

datetime[(fsp)]

保存日期和时间，范围从1000-01-01 00:00:00.000000到9999-12-31 23:59:59.999999。 fsp的范围是从0到6，默认是0，用来指定小数秒的精度(fractional seconds precision)。

sql实例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 设置时区
set @@time_zone = '+8:00';

create table t_log_1
(
    id       int primary key auto_increment,
    message  varchar(32)       default '' comment '日志内容',
    log_time datetime not null default NOW() comment '日志记录时间'
);

insert into t_log_1(message, log_time)
values ('0', 0)
     , ('20200502', '20200502')
     , ('2020-05-02', '2020-05-02')
     , ('2020-05-02 19:56:09', '2020-05-02 19:56:09')
     , ('2020-05-02 19-56-09', '2020-05-02 19-56-09')
     , ('20200502195609', '20200502195609')
     , ('1970-01-01 00:00:01.000000', '1970-01-01 00:00:01.000000')
     , ('from_unixtime(1588427123)', from_unixtime(1588427123));
# 格式错误
#      , ('202005021956', '202005021956')#error
#      , ('2020050219', '2020050219')# error
#      , ('2020-05-02 195609','2020-05-02 195609')#error

select * from t_log_1;

datetime查询结果

timestamp[(fsp)]

保存时间戳，范围从'1970-01-01 00:00:01.000000' UTC到'2038-01-19 03:14:07.999999' UTC。
fsp的范围是从0到6，默认是0，用来指定小数秒的精度(fractional seconds precision)。

sql实例

和datetime类似

time[(fsp)]

保存的时间长度，范围从-838:59:59.000000到838:59:59.000000。
fsp的范围是从0到6，默认是0，用来指定小数秒的精度(fractional seconds precision)。

year[(4)]

保存年份，范围从1901到2155，或者是0000

timestamp和datetime的异同

相同之处

保存的都是日期和时间，都是YYYY-MM-DD HH:MM:SS[.fraction]的数据

不同之处

1.范围不同

  • datetime的范围是1000-01-01 00:00:00.000000到9999-12-31 23:59:59.999999
  • timestamp的范围是1970-01-01 00:00:01.000000到2038-01-19 03:14:07.999999

2.保存方式不同

  • timestamp会把时间先转到UTC时区，然后保存。查的时候再转换到连接的当前时区。
  • datetime就是直接存

sql对比

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

# 设置时区到UTC
set @@time_zone = '+0:00';
create table t_log_3
(
    id      int primary key auto_increment,
    message varchar(32)        default '' comment '日志内容',
    log_dt  datetime  not null default NOW() comment '日志记录时间datetime',
    log_ti  timestamp not null default NOW() comment '日志记录时间timestamp'
);
insert into t_log_3(message, log_dt, log_ti)
values ('2020-05-02 19:56:09', '2020-05-02 19:56:09', '2020-05-02 19:56:09');

# UTC时区下查询
select * from t_log_3;

# 设置时区到+8:00再查询
set @@time_zone = '+8:00';
select * from t_log_3;

UTC时区下查询： UTC时区下查询

+8:00时区下查询: +8:00时区下查询

时区的设置

查看当前的时区

show variables like '%time_zone%';

时间配置

  • 全局：select @@global.time_zone;
  • 当前连接：select @@session.time_zone;

修改当前连接时区

set @@time_zone = '+8:00'，只有当前连接有效，重新连接之后或者别的连接都不会有这个配置

修改全局时区

set global time_zone = '+8:00'，改完之后，连接需要重新连接才能生效

查看效果

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);，查看当前时间和UTC时间差多少

查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 订单表
create table t_order
(
    id         int primary key auto_increment,
    num        int      not null comment '订购数量',
    order_time datetime not null default NOW() comment '订购时间'
);

insert into t_order(num, order_time)
values (5, '2019-12-30 00:00:00'),
       (3, '2020-01-13 00:00:00'),
       (20, '2020-02-13 00:00:00'),
       (25, '2020-03-13 00:00:00'),
       (35, '2020-03-13 00:00:00');

查看某天的订单

1
2
3
4
5
6

# 今天
select * from t_order where to_days(order_time) = to_days(now())
# 指定日期格式2
select * from t_order where to_days(order_time) = to_days('20210306')
# 指定日期格式2
select * from t_order where to_days(order_time) = to_days('2021-03-06 15:00:36.377')

2020年的订购总数

1
2
3

select sum(num) as 'total', year(order_time) as `year`
from t_order
where year(order_time) = 2020;

2020年的订购总数

20200101到20200229的订单

1
2
3
4

select *
from t_order
where order_time >= '20200101'
  and order_time <= '20200229';

20200101到20200229的订单

返回订单的时候，给定datetime格式

1
2

select num, DATE_FORMAT(order_time, '%Y%m%d %H:%i:%s')
from t_order;

返回订单的时候，给定datetime格式

function

这里列举主要使用的几个，更多的可以看参考资料的链接。

function作用
now[(6)]当前时间
year()返回年份
DATE_FORMAT()时间格式
FROM_UNIXTIME()时间戳转为时间
UNIX_TIMESTAMP()时间转为时间戳

数据格式转换

  • datetime(timestamp)转为date，CAST(datetime_col AS DATE)
  • datetime(timestamp)转为数字，直接+0即可，SELECT NOW(), NOW()+0, NOW(3)+0;
  • 修改时间加上一小时，或者12小时之前，select date_sub(now(), interval 24 hour) as now，select date_add(now(), interval 1 hour) as now;

参考资料