MySQL: automatic mapping of UTC to Local Timezone

Question

I have a forum where users can post comments. When a comment is created its corresponding datetime value is stored in UTC format.

I intend to present the data in local time, say 'ASIA/SINGAPORE';

2 options:

1. use convert_tz each time querying the database. I dont like the approach, cause it makes me rewrite the select_expr each time querying.
2. use SET time_zone = 'ASIA/SINGAPORE';

As for the second option, I want to know what is the validity scope of the command (no super privilege here). more specifically, say if i'm using a php application, does the config gets invalid as i close db connection? should i issue the command each time querying the db?

Tnx.

Answer 1

MySQL variables are scoped in the connection (lowest level, between libmysql <-> mysqld). It means, that if PHP itself or some application library uses any kind of mysql connection pooling, then you could observe this variable disappearing (because of invisible connection switching), and the variable definitely will disappear after disconnecting.

If you are not happy rewriting your query, you probably could select apropriate tz name on the fly -- say, form a users table, as long as you have the id of the logged user, like this:

SELECT convert_tz( ..., ..., (select user_tz from users where user_id = ...))