First and foremost, don’t forget that the server is set to UTC (+00:00). You can no longer get away from throwing in your own timestamps, quit bitching, move to UTC, be done with it…
This became an issue with trying to return results with averages attached. Averages of (edit: NONE of this was working efficiently, it was taking ~15sec to pull data with averages attached, probably going to go back to the old system and pull them separately.)temperature, pressure, humidity and other items will be greatly beneficial when it comes to displaying data on the application. Mysql’s now()returns the machine’s local time in the timezone it is set for. Any attempt to change the code to return the results properly just offsets the times of the records returned (ie: local time: 2000 HRS was returning results from 1600 HRS, which is -04:00 difference, the difference between UTC and EDT…)
And now I’m not longer so worried about UTC headaches, thanks mysql, the great humbler that you are…
So, I bit the bullet and added a new column to the bmesensor table that automatically timestamps a new addition in UTC. This also timestamped all of the existing data to the then current time…
I re-indexed the tables to sort by sensor and
Here’s the other major accomplishment for the day, joining tables in mysql:
SELECT a.sensor, b.location, b.name, a.datetime, a.sys_datetime, a.temperature, a.pressure, a.humidity, a.dewpoint, a.voltage, a.rssi FROM bmesensor a LEFT JOIN sensor_name b USING(sensor) WHERE sensor=$sensor ORDER BY a.sys_datetime DESC LIMIT 1