Ok – I’ve dug into the code and the database structure and I see why this is not working. I’ll use an example as this will be the most clear. I am in the EST timezone which is UTC -5 hours.
Assume only 2 page hits on March 2nd (EST).
- March 2nd, 6:50pm (EST) -> March 2nd, 11:50pm (UTC)
- March 2nd, 7:10pm (EST) -> March 3rd, 12:10am (UTC)
The code deconstructs the date and time and stores them in two separate fields in the database – namely DATE and TIME and stores the UTC values.
If I look at the usage stats on March 2nd, 8:00pm (EST) – I would expect to see 2 hits as per the above information. What actually happens is that the widget takes my current time – March 2nd, 8:00pm (EST) and changes it to UTC – March 3rd, 1:00am (UTC). It then grabs the date portion only, and runs a query on the database for all hits on March 3rd – getting only 1 record.
In order to get all of the hits for March 2nd (EST), we actually need to query from March 2nd, 5:00am (UTC) to March 3rd, 5:00am (UTC).
In my opinion – the data being stored in the database is correct. It is the database structure that makes it difficult to extract (breaking up date and time) and the queries that are wrong.
To fix this we need to:
1) Create a timestamp field that is the combination of DATE and TIME
2) Update the queries to use the timezone adjusted values, as opposed to simply DATE, etc.
OR… Get rid of UTC altogether and just store it in the timezone specified by wordpress and/or php.ini.
Sean.
