I've done a bunch of research and I can't seem to find a suitable answer for this issue without hacking which I'd like to avoid.
My client needs the entries in the DB to always assume they're relative to the users current timezone. So content set to display at 9am on Monday morning will display at 9am Monday in every timezone.
The locale options in Twig only adjust the UTC timestamp to the users current timezone but it's still displaying content based on UTC timestamp in the DB.
Through MySQL I can use the following command per session to accomplish what I'm looking for:
SET SESSION time_zone = '+10:00';
This appropriately offsets the MySQL time to match a users timezone +10 hours ahead of UTC, in this case Australia.
Reading this article it seems that "initSQLs" might be the answer but it's a Yii article that talks about adding that command to the db array within the protected/config/main.php file which I'm unsure of how to tie into.
Nevertheless, I'm hoping someone might have some insight into how I can bootstrap the DB calls per user session or another solution potentially utilizing a plugin.
We're doing timezone lookups by IP address, I realize the potential issue, so the option will need to work for both logged in and logged out users.