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,
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
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.
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
How would I fix this? What new query will I need for the videos downloaded?
Regards. [mySQL noob]