SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Italy
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Grouping by day on Unix time

    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

    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. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    By "Unix time format" do you mean a Unix timestamp? Stored in an integer column?

    Code:
    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/...functions.html

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Italy
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    By "Unix time format" do you mean a Unix timestamp? Stored in an integer column?
    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. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Group by both the month and the year. You will have a different row for 2009,12 and 2010,12.

    Code:
    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. #5
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Italy
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dan,
    Just shows I am not very good at this


    Adrien


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •