0

First, Thankyou for sparing your precious time to look upon my beginer question

So the thing is, I still can't fathom how datetime work on MySQL

"Can I, upon changing timezone, convert all datetime columns in all tables to match the new timezon?"

For example, my server local time is UTC +7 I have database with several tables which some contain column with datetime data type (eg. CreationTime, updatedTime, visitTime, etc) Now supposed to, somehow, I plan to move my server to a different timezone, let say UTC +8. Can I do one method to convert all the datetime columns in many tables automatically to the new timezone? let say the old datetime is 22/2/20 18:00:00, after the conversion, become 22/2/20 19:00:00. All with a method or functions (preverabli native to mysql, but i can compromised using php), considering that manually writing a list of all tables or all columns name one by one is impossible?

Sorry if the question is superficial. I have exploring the web, and stackoverlow, but my meager knowledge in programming just can't graps most of the discussion.

Thank you very much.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gita Prasetya Adiguna
  • 269
  • 6
  • 13

1 Answers1

1

The same question was here: Should MySQL have its timezone set to UTC?

Based on these answers you can change /etc/mysql/my.cnf file to change default database timezone:

default_time_zone='+00:00'

or

 timezone='UTC'

Notice: The right way is to save all timestamp data in UTC+0 format (backend), and then fetch it with actual client timezone (frontend).

Vladimir
  • 441
  • 1
  • 4
  • 14
  • That "notice" is incorrect in my view. It's often repeated as if it's a silver bullet for all time zone work - it's not, specifically for data in the future. It's fine for timestamps, but not for user-entered data which may have a natural time zone. See https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/ – Jon Skeet Feb 24 '20 at 08:03
  • @JonSkeet so you need to get the client`s timezone before save it to DB. Simple. – Vladimir Feb 24 '20 at 08:12
  • 1
    I don't think you've appreciated the subtlety highlighted in the blog post. You can't accurately store the UTC instant of a future time if the time zone rules change between the storage time and the time of the event itself. – Jon Skeet Feb 24 '20 at 08:36
  • @JonSkeet we doing that trick all the time in mobile apps development. We have users all over the world, so, we need to show dates in actual users timezones. As a backend developer, I receive date in unix timestamp (iOs and Android specifics. That dates must be in UTC+0 TZ and store it in date/time timestamps in DB). All api responses in timestamps also. In frontend (mobile apps and webs) all dates decoding in actual user TZ. So, if phone will change to another TZ, all dates in apps willl change (couse it views only). That works perfect in two ways. – Vladimir Feb 24 '20 at 08:48
  • 1
    No, I still don't think you've understood the point of the blog post. If you're trying to store data for an event in the future in a particular time zone, all you can go on is the *predicted* UTC offset based on the rules we know about now. Those rules can change between now and the time of the event. This isn't about the phone changing to a different time zone - it's about the rules of the existing time zone changing. It's all painstakingly laid out in the blog post. – Jon Skeet Feb 24 '20 at 08:51
  • Well it seems spark quite a debate. So thinking all about it, would it be better to use int instead of datetime? Considering using PHP,. Like storing the data using time(), then retrieve it and parse it using date() to show it in server or client timezone? Or is there benefit of using datetime that I am unaware about? – Gita Prasetya Adiguna Feb 24 '20 at 18:52
  • @GitaPrasetyaAdiguna: I would probably stick with the database date/time types, to make it easier for everyone to understand - but I *wouldn't* change all your database values to match your server's local time zone. Using UTC is a good choice in many situations - just not all of them. We don't know enough context about what you're doing to say whether it's appropriate for you. But usually when it's *not* appropriate, that's because you'd be storing a time zone ID as a separate field (and possibly a normalized UTC value for optimization, to be recomputed if rules change). – Jon Skeet Feb 25 '20 at 18:38
  • I don't think this answer has answered the question at all. The original question appears to be asking how to mutate the stored DATETIME values globally. MySQL DATETIME column does not contain timezone information. Changing the database timezone has no impact on the stored *DATETIME* values (TIMESTAMPs are a separate story). – nogridbag Dec 01 '21 at 19:59