SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql query for different date types

    http://www.youtubetomp3.net/statistics

    The top stats Videos Converted, that query runs off unix time in db.

    The bottom stats Videos Downloaded, runs off normal date like 2009-02-17 18:48:04.

    I have 2 queries,

    Code:
    For the Videos Converted-
    SELECT DATE(FROM_UNIXTIME(utime)) AS theday, COUNT(id) AS thecount FROM `converted_videos` WHERE `utime`>=UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 30 DAY) GROUP BY theday ORDER by theday DESC
    Code:
    For the videos downloaded-
    SELECT DATE(datetime) AS theday, COUNT(id) AS thecount FROM `downloaded_files` WHERE `datetime`>=UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 30 DAY) GROUP BY theday ORDER by theday DESC
    The videos downloaded is 1 hour infront of the videos converted, so at 11pm the videos downloaded is in new day, whereas videos converted is in previous still.

    How would I fix this? What new query will I need for the videos downloaded?

    Regards. [mySQL noob]
    Last edited by Latox; Feb 27, 2009 at 23:04.
    :-)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    best thing to do would be to fix your code which assigns the times
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its running on a script that has a current of 30,000 rows in the database, inserting over 20,000 values daily...

    Can I not do something like -/+ 3600?
    :-)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dyfuCa View Post
    Can I not do something like -/+ 3600?
    yes, but do it while inserting, not while selecting
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I need to do it on the already inserted values though.
    :-)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you'll want to use an UPDATE statement, then
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict Latox's Avatar
    Join Date
    Dec 2008
    Location
    Australia
    Posts
    389
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you can update 2009-02-17 18:48:04 as a unixtime, surely you can select it as one.
    :-)


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
  •