CODEWITHSUNDEEP
phpmysqlsqltimezone
jimmy
jimmy

Reputation: 411

How to compensate for MySQL timezone difference?

I store times in MySQL sent from a PHP script as CURRENT_TIMESTAMP. This makes times from the wrong timezone, minus 1 hour from where I am. I'm not superuser, so SET GLOBAL time_zone = 'Europe/London'; won't work. Is there anyway I can modify the input or output query to compensate 1 hour?

This is my current sql query, sent from a form:

REPLACE INTO `order_admin_message` (`order_id`, `message`, `date_updated`)
VALUES ('$number', '$msg', CURRENT_TIMESTAMP)

And then I retreive it using:

SELECT order_admin_message.message, order_admin_message.date_updated
FROM order_admin_message
WHERE order_admin_message.order_id = $number

EDIT: To be clear, I don't want to show the user's time, just local London time (taking daylight saving into account in summer). EDIT 2: Changed the subject to be closer to the question/answer.

Upvotes: 1

Views: 732

Answers (3)

PenguinCoder
PenguinCoder

Reputation: 4357

In PHP, just change it for your display. Don't store locale dependent dates or times in a database. Makes conversion later on, a PITA. Just display the time/timezone you need even if you don't care about the user.

$tz = new DateTimeZone('Europe/London');

$datetime_updated = new DateTime($results['order_admin_message.date_updated']);
$datetime_updated->setTimezone($tz);
$display_date = $datetime_updated->format("M j, Y g:i A");

echo $display_date;

Upvotes: 2

Nin
Nin

Reputation: 3020

Use the CONVERT_TZ() function: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz

Upvotes: 0

Ben
Ben

Reputation: 35613

Use utc_timestamp instead, and convert to the timezone of the user yourself.

UTC_TIMESTAMP is the current UTC date and time, as recognised all over the world.

As long as you know where your user is and what his timezone is, you can convert it by adding the correct offset.

If you don't know what the user's desired timezone is, then you have a different problem - basically you need to find out somehow.

Upvotes: 0

Related Questions