SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    AK
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Extracting Yearly data

    Hello,

    I want to be able to get last years and the current years data only from a data base that has a MySQL timestamp. Following is how I do it now:

    PHP Code:
    $LastYearData "SELECT SUM(Data) AS sum FROM MyTable WHERE DataDate between '1104570000' and '1136019600'";

    $ThisYearData "SELECT SUM(Data) AS sum FROM MyTable WHERE DataDate between '1136106000' and '1167555600'"
    This does work but it makes me go in every year and change these values. I would likle a way to have it automatically get last years data and this years data automatically. Any ideas?

    Thanks in advance.

  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)
    do it in one single query --
    Code:
    select sum(case when DataDate >= unix_timestamp(date_add(current_date, interval 1-dayofyear(current_date) day))
                     and DataDate <  unix_timestamp(date_add(date_add(current_date, interval 1-dayofyear(current_date) day), interval 1 year))
                 then Data else 0 end 
              ) as ThisYearData
         , sum(case when DataDate >= unix_timestamp(date_add(date_add(current_date, interval 1-dayofyear(current_date) day), interval -1 year)))
                     and DataDate <  unix_timestamp(date_add(current_date, interval 1-dayofyear(current_date) day)
                 then Data else 0 end 
              ) as LastYearData
      from MyTable      
     where DataDate >= unix_timestamp(date_add(date_add(current_date, interval 1-dayofyear(current_date) day), interval -1 year)))      
       and DataDate <  unix_timestamp(date_add(date_add(current_date, interval 1-dayofyear(current_date) day), interval 1 year))
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    AK
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    WOW! I appreciate the reply but I don't think that is exactly what I was looking for. I was hoping for something more simpler such as in common language--- "Give me the total of a certain column for all of last year and a total of the same column for this year." Then when '07 appears it'll do the same thing with out me having to go in and re-input the new unix-timestamp data. I am trying to get away from editing my web page every year with the last year and this year unix timestamp. I just want the last years data and the current years data. I looked for a "year" command but really didn't see one that may work. Anyway, thanks.

  4. #4
    SitePoint Zealot Seriph's Avatar
    Join Date
    Oct 2005
    Location
    San Diego, CA
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://dev.mysql.com/doc/refman/5.0/...functions.html

    Use FROM_UNIXTIME() and YEAR(), and NOW() .. this is totally untested...

    Current Year...
    PHP Code:
    $LastYearData "SELECT SUM(Data) AS sum FROM MyTable WHERE YEAR(FROM_UNIXTIME(`DataDate`)) = YEAR(NOW())" 
    Last Year
    PHP Code:
    $LastYearData "SELECT SUM(Data) AS sum FROM MyTable WHERE YEAR(FROM_UNIXTIME(`DataDate`)) = (YEAR(NOW())-1)" 
    Might need a GROUP BY statement in there, not sure.

  5. #5
    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 lkirgan
    WOW! I appreciate the reply but I don't think that is exactly what I was looking for.
    then all i can suggest is that you actually try my query

    it will work correctly, doing just what you asked, this year, next year, any year

    furthermore, it will be efficient and use the index, if any, on the DataDate column, whereas Seriph's query will require table scans

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  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)
    if you are still skeptical that my query works, try running this --
    Code:
    select (date_add(current_date, interval 1-dayofyear(current_date) day))
         , (date_add(date_add(current_date, interval 1-dayofyear(current_date) day), interval 1 year)) 
         , (date_add(date_add(current_date, interval 1-dayofyear(current_date) day), interval -1 year))
         , (date_add(current_date, interval 1-dayofyear(current_date) day))
    this will give you the following values:

    2006-01-01,2007-01-01,2005-01-01,2006-01-01

    now try putting those values into the unix_timestamp functions:
    Code:
    select unix_timestamp(date_add(current_date, interval 1-dayofyear(current_date) day))
         , unix_timestamp(date_add(date_add(current_date, interval 1-dayofyear(current_date) day), interval 1 year)) 
         , unix_timestamp(date_add(date_add(current_date, interval 1-dayofyear(current_date) day), interval -1 year))
         , unix_timestamp(date_add(current_date, interval 1-dayofyear(current_date) day))
    this will give you the following values:

    1136091600,1167627600,1104555600,1136091600

    more comfortable now?

    one final test, reset your computer's clock to some day in 2007 and run them again to see that they do change correctly
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot Seriph's Avatar
    Join Date
    Oct 2005
    Location
    San Diego, CA
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ew table scans. I'm no SQL expert, go with r937

  8. #8
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    then all i can suggest is that you actually try my query

    it will work correctly, doing just what you asked, this year, next year, any year

    That's always the tricky part, getting people to try stuff, even AFTER 10,000+ posts.

  9. #9
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    AK
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face

    Oh there's no doubt that r937 knows what he's talking about because he has helped me in the past with flying colors. I just wanted to make sure that I was clear about what I was trying to do but after seeing his example it appears I explained my intentions just fine. I appreciate every reply to this topic and I will diffinitely give the code a try tonight. Thank you very much for your time and much desired input.

  10. #10
    SitePoint Enthusiast
    Join Date
    Jan 2004
    Location
    AK
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It works beautifully!!! Thank you very much!


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
  •