CODEWITHSUNDEEP
phpmysqltimezone
bpmccain
bpmccain

Reputation: 600

Showing MySQL timestamps in local time

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?

Upvotes: 4

Views: 2420

Answers (3)

Mike Purcell
Mike Purcell

Reputation: 19999

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.

Upvotes: 0

Wesley
Wesley

Reputation: 2200

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();

Upvotes: 2

Tarik
Tarik

Reputation: 81781

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

Upvotes: 2

Related Questions