Showing MySQL timestamps in local time

Question

I have a MySQL database with a lot of data stored with timestamps all in UTC time.

When a user queries that database, I want them to be able to view the data but in their local timezone.

I'd prefer not to have to calculate an offset each time and change the timestamp with PHP code. Is there any other way?

Answer 1

Where I work we support clients worldwide and have found it easier to even set the server's system time to UTC. This allows us to ensure consistent logging and base service timestamps, and if we need to add servers to a pool in a different timezone, we don't have to worry about if localtime is set correctly (b/c it's already UTC).

However there is a cost, as the OP mentioned, our application has to calculate the timezones. We assign a timezone to our clients, and a user signing into a client site gets the client's timezone, so we have to calculate date/timestamps for presentation, every time. We do profile our apps on occasion and these timezone conversions barely register.

At the end of the day it's really up to you and your situation when dealing with timezone conversions.

Answer 2

You can use localtime() function to get the local time and find the matching records from database.

Answer 3

It is possible to set a timezone per connection with mysql like so:

SET time_zone = 'America/Los_Angeles';

or

SET time_zone = '-8:00';

But for me best solution would be localtime();