为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
- 【TiDB 版本】:v4.0.5
- 【问题描述】:
问题复现如下:
表结构:
CREATE TABLE `t_a` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`type` tinyint(4) NOT NULL,
`create_time` int(11) NOT NULL,
PRIMARY KEY (`id`)
)
示例数据如下:
select * from t_a
id type create_time
------ ------ -------------
1 1 1601004020
2 1 1598412094
3 1 1598498511
4 2 1598584933
5 3 1599794551
6 3 1599880969
SQL语句如下:
select from_unixtime(create_time,'%Y-%m-%d') as t_day,count(*) as cnt
from t_a
where `type` = 1
group by t_day
union all
select from_unixtime(create_time,'%Y-%m-%d') as t_day,count(*) as cnt
from t_a
where `type` = 3
group by t_day
查询结果:
t_day cnt
-------- --------
2020-09- 1
2020-09- 1
2020-09- 1
2020-08- 1
2020-08- 1
现象是日期列被截断了,没有显示完全
如果不用from_unixtime的格式化,而是使用date_format格式化,日期显示就没问题
select date_format(from_unixtime(create_time),'%Y-%m-%d') as t_day,count(*) as cnt
from t_a
where `type` = 1
group by t_day
union all
select date_format(from_unixtime(create_time),'%Y-%m-%d') as t_day,count(*) as cnt
from t_a
where `type` = 3
group by t_day
查询结果如下:
t_day cnt
---------- --------
2020-09-11 1
2020-09-12 1
2020-08-26 1
2020-08-27 1
2020-09-25 1
不知道是不是from_unixtime格式化日期,函数解析出了问题,请大佬分析一下