SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2004
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order by date when date is int value

    Hi,

    I have a table with the date filed entered as INT, so for example 1262817810 as date value.

    I'd like to retrieve the rows grouped by day, so I'll need to convert the INT value of my date to a day value, because now every value is different, as it is counted in seconds.

    Code:
    SELECT date FROM revenue GROUP BY DAY(date)
    I hope I my make my question clear.

    All help really apppreciated!

    Michel

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    when you say "retrieve the rows grouped by day" what exactly do you mean?

    GROUP BY performs aggregation, i.e. it collapses multiple table rows into one result row, and the result row is not the same as a table row

    are you sure you want to do grouping? if so, what aggregate value(s) do you want to return? earlest time in the day? average time in the day? latest time in the day? number of time values in the day?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2004
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    thank you for helping.

    I'll try to explain. I have a table with data about revenue. Every column has a customer, a date and revenue (and other fields). The date field is an INT, based on the second the row has been entered. In a report, I'd like to show, per day, the total revenue per customer.

    So:

    11/11/2011
    Customer 1 500€
    Customer 2 400€

    12/11/2011
    Customer 3 600€
    Customer 2 300€

    ...


    The problem is that my date filed is detailed to the second,a nd I want to show it per day.

    So I thought I needed to group it by DAY(date). But maube I need sth else?

    Hope that helps,
    Michel

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    weird, we had just recently a (long, tortured, and semi-hijacked) thread with exactly the same premise...

    http://www.sitepoint.com/forums/show...illion-records

    my advice to you is the same as i gave early in that thread (post #12) --

    all you need for your query is a simple SELECT, one that returns the rows you want in timestamp order
    Code:
    SELECT `date`
         , other_columns 
      FROM revenue 
    ORDER
        BY `date`
    then, in your php code (if php is what you're using), look at each returned row and compare it to the previous row, and when the day changes, print the new day as a header before printing that result row

    if i did php, i'd mock up the code for you, but i don't (i do coldfusion instead)

    "current/previous" logic is actually fairly common, and as far as detecting a date change, i'm sure php has the appropriate functions

    p.s. note that i put `date` in backticks, because DATE is a reserved word in mysql... you'd be better off renaming that column
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2004
    Posts
    418
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, thx. I'll fix it in PHP, no problem. I thought there would have been a SQL function to do that in one go.

    Thx for the help,
    Michel

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by webnology View Post
    I thought there would have been a SQL function to do that in one go.
    no, there isn't

    this "function" would have had to produce two completely different types of result row --

    11/11/2011
    Customer 1 500€
    Customer 2 400€

    12/11/2011
    Customer 3 600€
    Customer 2 300€

    that's a nested or hierarchical output display, isn't it
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Can you have multiple entries for revenue per customer per day?
    IE can you have:
    Cust1 = 1, date = 1262817810, revenue = 500.45
    Cust1 = 1, date = 1262817900 revenue = 201.38

    ??

  8. #8
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,603
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    Why are you storing your dates as ints rather than as dates? If you stored them as dates you'd be able to use the built in functions to extract the date in whatever format you want.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    stephen, when they're stored as ints, you are equally able to use the built in functions to extract the date in whatever format you want
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •