DateTime group by date and hour

Question

I have a datetime called activity_dt and the data looks like this:

2/5/2013 9:24:00 AM
2/7/2013 7:17:00 AM

How do i group by date and hour?

what rdbms that you used? – Iswanto San Feb 13 '13 at 03:38 — Iswanto San, Feb 13 '13 at 03:38

score 145 · Accepted Answer · answered Feb 13 '13 at 03:49

145

SQL Server :

SELECT [activity_dt], count(*)
FROM table1
GROUP BY DATEPART(day, [activity_dt]), DATEPART(hour, [activity_dt]);

Oracle :

SELECT [activity_dt], count(*)
FROM table1
GROUP BY TO_CHAR(activity_dt, 'DD'), TO_CHAR(activity_dt, 'hh');

MySQL :

SELECT [activity_dt], count(*)
FROM table1
GROUP BY hour( activity_dt ) , day( activity_dt )

answered Feb 13 '13 at 03:49

Iswanto San

18,263
13
58
79

do you mean format the date like 2/5/2013 ? SELECT DATE_FORMAT( activity_dt , '%e/%c/%Y' ) – Iswanto San Feb 13 '13 at 04:05
1

Why do you use the square brackets? On mysql it's giving me a syntax error when I use them – Jazerix Mar 06 '14 at 13:42
1

@Jazerix: you don't need that..Actually square brackets is used if your column name contains space like `[activity dt]` – Iswanto San Mar 06 '14 at 14:18
@IswantoSan - what's the best way to display the date & hour with each row for mysql? – tylerSF Jul 15 '16 at 16:03
6

Does not work in ms sql: Column 'Invoices.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – CularBytes Apr 02 '19 at 12:45
@IswantoSan how can we define start time 8:00 and end time 18:00 – Gayan Chaturanga Jan 21 '20 at 09:20
@IswantoSan how we define start time and end time rang using am and pm? – Gayan Chaturanga Jan 21 '20 at 09:59
this is incompatible with sql_mode=only_full_group_by – Alexey Sh. Aug 18 '23 at 11:05

Simon · Answer 2 · 2016-09-09T08:26:33.283

25

Using MySQL I usually do it that way:

SELECT count( id ), ...
FROM quote_data
GROUP BY date_format( your_date_column, '%Y%m%d%H' )
order by your_date_column desc;

Or in the same idea, if you need to output the date/hour:

SELECT count( id ) , date_format( your_date_column, '%Y-%m-%d %H' ) as my_date
FROM  your_table 
GROUP BY my_date
order by your_date_column desc;

If you specify an index on your date column, MySQL should be able to use it to speed up things a little.

edited Sep 09 '16 at 08:26

answered Jan 08 '16 at 09:54

Simon

3,580
2
23
24

I had to replace %h with %H, sorting with 0-24 works better for me instead of 0-12 & 0-12. – Melvin Apr 03 '16 at 09:24
Just a note regarding index usage. While date_format does allow indexes to be used, indexes will only be optimally used (loose index scan) when specific/limited aggregate functions are used. Generally, only MIN() and MAX() applied to a single column allow for index scans, and also other aggregates as long as they specify DISTINCT keyword. Source: [link] (http://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html) – JavoSN Jun 26 '16 at 19:21

score 1 · Answer 3 · answered Feb 13 '13 at 03:45

1

SELECT [activity_dt], COUNT(*) as [Count]
  FROM 
 (SELECT dateadd(hh, datediff(hh, '20010101', [activity_dt]), '20010101') as [activity_dt]
    FROM table) abc
 GROUP BY [activity_dt]

answered Feb 13 '13 at 03:45

DevT

4,843
16
59
92

score 1 · Answer 4 · answered Aug 13 '20 at 09:14

1

In my case... with MySQL:

SELECT ... GROUP BY TIMESTAMPADD(HOUR, HOUR(columName), DATE(columName))

answered Aug 13 '20 at 09:14

ggrandes

2,067
22
16

DateTime group by date and hour

4 Answers4

Linked

Related