Grouping by day on Unix time

Databases
1

Hi,
I have a difficult question and I’m not sure it’s possible to do what I have in mind, but if anyone knows s/he will be on this forum :slight_smile:

I have a “sales” table in which each row contains the details for each sale (ie one sale = one row). The date in which the sale was made is recorded in Unix time format.

Is there a way, using only MySql, to see how many sales were made per day/week/month…?

Thanks,
Adrien

2

By “Unix time format” do you mean a Unix timestamp? Stored in an integer column?

SELECT
  DATE(FROM_UNIXTIME(your_int_column)),
  COUNT(*)
FROM
  table
GROUP BY
  DATE(FROM_UNIXTIME(your_int_column))

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

3

Hi Dan,
Thanks for the quick reply, this is great, it’s exactly what I needed. I looked at the documentation you indicated and was able to modify your query to get the same data divided by month. However I cannot seem able to get MySql to distiguish between months of different years, ie December 2010 and December 2009. Is there a way to do this?

Thanks,
Adrien

4

Group by both the month and the year. You will have a different row for 2009,12 and 2010,12.

SELECT
  YEAR(FROM_UNIXTIME(your_int_column)),
  MONTH(FROM_UNIXTIME(your_int_column)),
  COUNT(*)
FROM
  table
GROUP BY
  YEAR(FROM_UNIXTIME(your_int_column)),
  MONTH(FROM_UNIXTIME(your_int_column))
5

Thanks Dan,
Just shows I am not very good at this :slight_smile:

Adrien