MySQL datetime with different time zones

Question

I have a DateTime object in mysql database containing for example this datetime "2017-08-14 18:06:40" and the server/mysql default timezone is USA so when i insert a datetime it goes in US time zone .

My problem is when a user from let's say Europe select that datetime , it will be different because of the timing difference isn't so ?

How do I solve this problem. I want the user in Europe to get the datetime relative to their timezone

I found from searching this

CONVERT_TZ(`comment_date`,@@global.time_zone,@@session.time_zone) AS comment_date,

Then I found that you need to set @@session.time_zone its not detected automatically

So how do we solve this and what are the option and techniques that sites or apps uses to unification datetime across countries?

Answer 1

You should change the datatype to timestamp instead of datetime if you want it to represent an instant moment in time regardless of timezone where it was saved. Timestamp is always saved as UTC while datetime represents a local date and time and does not contain any zone information.

See this question for more information: Should I use field 'datetime' or 'timestamp'?

See also Mysql reference manual regarding datetime