CODEWITHSUNDEEP
mysqltimezone
developarvin
developarvin

Reputation: 5069

MySQL - Column specific time zone during insert

Let's say I have 2 TIMESTAMP fields/columns, event and stamp. For event field, when we insert, I want to set time zone so that event will use user specified time zone. However, I don't want stamp to use that time zone. I want stamp to use whatever default time zone the server is using.

SET time_zone = <UTC offset>;

does not work since it will affect both event and stamp where we only want event to be affected by the time zone.

Is there a way to set time zone for specific columns during insert?

Upvotes: 3

Views: 1756

Answers (2)

Zheng Kai
Zheng Kai

Reputation: 3635

SET time_zone = <user timezone>;    
INSERT ... SET event = CURRENT_TIMESTAMP(), stamp = UTC_TIMESTAMP(), ...

or read more at http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Upvotes: 0

Ja͢ck
Ja͢ck

Reputation: 173642

MySQL doesn't bother about timezones when you insert a date.

I would recommend storing the date/time in GMT (+00:00) and have another field that stores the timezone used (e.g. "Europe/Amsterdam").

Edit

I'm not entirely sure you would need the used timezone though, since you can format a GMT time in any way you'd like inside your scripts / apps.

Upvotes: 1

Related Questions