2

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?

chanafdo
  • 5,016
  • 3
  • 29
  • 46
Saef Myth
  • 287
  • 8
  • 19

1 Answers1

7

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

stenix
  • 3,068
  • 2
  • 19
  • 30
  • will you can use datetime object just dont insert the value ,set the default value to current time stamp ,,and set the mysql to UTC time , then parse the date on client side to the default timezone – Saef Myth Aug 15 '17 at 12:21