1

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.

Qwofer
  • 73
  • 6

5 Answers5

1

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.

Menelaos
  • 23,508
  • 18
  • 90
  • 155
1

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));
denny
  • 2,084
  • 2
  • 15
  • 19
  • May I request you to please add some more context around your answer. Code-only answers are difficult to understand. It will help the asker and future readers both if you can add more information in your post. – RBT Jan 20 '17 at 16:34
0

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.

Community
  • 1
  • 1
Arranun
  • 1
  • 1
  • 2
0

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
-1

Maybe this could help you.

SELECT COUNT(*) FROM TABLE t GROUP BY DATE(t.createddatetime)
Compo
  • 36,585
  • 5
  • 27
  • 39
Gokulraj
  • 21
  • 4