CODEWITHSUNDEEP
mysqlsqlinnodb
Qwofer
Qwofer

Reputation: 73

How to get count by day in My SQL

This table contains count of orders by hour in time stamp:

orders

id, created_at, items
1, 1484827200, 5
2, 1484830800, 10
3, 1484913600, 10

// how to get count of items by day? something like that:

2016-01-19, 15
2016-01-20, 10

my query:

SELECT sum(items)
FROM orders
GROUP BY EXTRACT(DAY_HOUR FROM created_at)

is not correct.

SELECT day(from_unixtime(created_at)), sum(items)
FROM orders
GROUP BY day (from_unixtime(created_at))

too slow.

Upvotes: 1

Views: 1228

Answers (5)

Rick James
Rick James

Reputation: 142208

I like to do variants on GROUP BY LEFT(datetime, 13). That expression returns a sortable/groupable string that works well for date+hour:

+---------------------+-----------------+
| NOW()               | LEFT(NOW(), 13) |
+---------------------+-----------------+
| 2017-01-20 14:44:42 | 2017-01-20 14   |
+---------------------+-----------------+

Of course, as already mentioned, you need to first convert from unixtime.

Upvotes: 0

denny
denny

Reputation: 2254

If you want sum by on hours then use this query

 SELECT id,created_at,SUM(items) FROM orders GROUP BY HOUR(FROM_UNIXTIME(created_at));

And If you want sum by on dates

 SELECT id,created_at,SUM(items) FROM orders GROUP BY DATE(FROM_UNIXTIME(created_at));

Upvotes: 1

Gokulraj
Gokulraj

Reputation: 21

Maybe this could help you.

SELECT COUNT(*) FROM TABLE t GROUP BY DATE(t.createddatetime)

Upvotes: -1

Menelaos
Menelaos

Reputation: 25727

1. Add a String Column called "Day" that is also indexed

Ideally you should include a day column, and update all your rows. Storing your datetimes as unixtimestamps and converting them every time causes the following problems:

  • You have to run from_unixtime for every row, and then also extract the day.
  • You are sacrificing any sort of optimizations that may work correctly with an indexed column. Unix timestamps are numbers and are essentially apples to oranges when compared to dates.

So I would just add a day column if this query is critical, and save the day along with the timestamp in order to get the maximum speed. Also index your day column.

2. Experiment

  • Try possibly using a aliased table, and then doing the group by. I have a feeling the optimizer optimizes in such a way that it may not make a different but you may be running from_unixtime 2 times instead of once (this needs to be checked):

    SELECT day, SUM(items) FROM (SELECT DAY(from_unixtime(created_at)) AS day, items FROM orders) AS temp GROUP BY temp.day

I'm not sure if the from_unixtime conversion was being run again in the group by.

Upvotes: 1

Arranun
Arranun

Reputation: 1

I found a question which could help you: How can I convert bigint (UNIX timestamp) to datetime in SQL Server?

One of the solution in the answers use 

Select
dateadd(S, [unixtime], '1970-01-01')
From [Table]

To change unixtime to a timestamp. It could be faster.

Upvotes: 0

Related Questions