按日期分组并在SQL中显示日期时间 [英] Group by date and show in date time in sql

问题描述

我要先对数据进行计数，然后再按日期分组(并以日期时间显示).数据类型的结果必须是日期时间而不是var char.有什么主意吗?

i want to count the data then group by date (and it show in date time). the result of data type must date time not var char. any idea ?

table_a

timestamp
2020-11-28 04:00:00
2020-11-28 05:00:00
2020-11-29 01:00:00
2020-11-29 02:00:00
2020-11-29 03:00:00

预期结果:

timestamp                 count
2020-11-28 00:00:00       2
2020-11-29 00:00:00       3

如果我查询以下内容:

select date(timestamp), count(*) as count
from table_a
group by date(timestamp)

结果是:

timestamp     count
2020-11-28     2
2020-11-29     3

推荐答案

您可以使用 DATE(timestamp)截断时间部分，然后使用该函数将其转换回 DATETIME CAST():

You can truncate the time part with DATE(timestamp) and cast back to DATETIME with the function CAST():

SELECT CAST(DATE(timestamp) AS DATETIME) AS my_datetime, 
       COUNT(*) AS count 
FROM table_a
GROUP BY my_datetime;

请参见演示.
结果:

See the demo.
Results:

> my_datetime         | count
> :------------------ | ----:
> 2020-11-28 00:00:00 |     2
> 2020-11-29 00:00:00 |     3

这篇关于按日期分组并在SQL中显示日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！