I have a data collection script which rolls raw data into a formatted table, based on year/month/day. I've been requested to roll up this raw data based on a different timezone - 3 hours behind - to better coincide with reports from a company in that timezone.
This is the rollup SQL query we currently run: SELECT DATE_FORMAT(date,'%Y%m%d'),sitecode,reseller,section,type,COUNT(DISTINCT ip), COUNT(ip) FROM rawdata WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW()) AND (DAYOFMONTH(date) = DAYOFMONTH(NOW()) OR DAYOFMONTH(date) = DAYOFMONTH(DATE_SUB(NOW(),INTERVAL 1 DAY))) GROUP BY 1,2,3,4,5 ORDER BY 1,2,3,4,5 Basically it summerizes the totals for today and yesterday. My question is, given that query, what is the best method to rollup this offset data? use DATE_SUB on the SELECT, or perhaps on the DAYOFMONTH? Any suggestions would be appreciated. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
