Timezone settings in MySQL - Using NOW()?

SOmewhat related to Doing calculations in MySQL vs PHP

Right now, our database assumes that the system time is in 开发者_JAVA百科UTC and uses that to calculate NOW(). PHP explicitly sets the timezone as UTC (so its impervious to server time zone shifts).

An accidental shift of timezones on the server messed this relationship up at the database level and i'm now trying to figure out the ideal congiguration:

1. configure Mysql to be in UTC, but also from the perspective that:

2. our application may be on someone else's server where they might have a different TZ (so i cant set the timezone at the mysql/server level). How do i configure it at the specific database level?

You should check out UTC_TIMESTAMP(); this ensures that a timestamp field is always UTC in the database. Have your PHP script do the offset from the users browser information. This can also be done using DATE_ADD/DATE_SUB or better CONVERT_TZ().

Have PHP set it before you store it in the database. Have it set to GMT and then calculate when displaying based on the users timezone as needed.