1

I am working on codeigniter application which have users from different time zones. Each user has some notifications based on some dates calculation.

So what should be best way to store date into Mysql? Either timestamp or datetime?

Ahmed Ashour
  • 5,179
  • 10
  • 35
  • 56
Manoj Maurya
  • 5
  • 3

4 Answers4

3

First off, you need to be aware of the locale setting for your mysql server. You want the server to be set to use UTC as a neutral setting. Any data that is stored in the mysql server will be relative to the server's timezone setting.

Unless you are dealing with future data (beyond the year 2032) or conversely really old data, the most efficient mysql datatype is the timestamp datatype. Just be careful to turn off the automatic mysql timestamp functionality when you don't want or need it.

Internally to PHP you want to utilize the DateTime class which includes ways to convert from any one timezone to another.

The missing ingredient that has not been mentioned, is that in order for this to work for end users, you need to store their timezone, or utilize functions in the browser to read from the operating system the current timezone of their workstation.

Timezone strings can be stored, and then used after you fetch data from the server, to then convert it and show it to them relative to their timezone.

But again to be clear, all data should be stored as UTC, and this requires that the server be configured to utilize UTC. Well it's a bit more complicated than that, but you can save yourself a lot of trouble when you insure there isn't a mismatch. By the same token your web/application servers (and in fact all servers) ought to be set to UTC, and of course to sync their time using NTP. Most cloud based servers are going to do this by default.

gview
  • 14,876
  • 3
  • 46
  • 51
0

Format doesn't matter as long as you keep your date as DataTime Object. Using PHP you can easily manipulate dates. My personal choice is using UTC offset because is easier for debug purpose. You can easier figure out if your time difference calculation is correct looking on the offset then on time zone name.

More information you will find under DataTime class.

Implementation is basically the same regardless format as long as you are using DataTime Object. This is the best way to calculate time differences in different time zones.

bobomam
  • 58
  • 7
0

With datetime you get a rich set of tools for interacting with the data (including converting between timezones) and the opportunity to handle dates prior to 1st Jan 1970 and after 19th Jan 2038.

Although the same tools are also applicable to TIMESTAMP, the automatic timezone conversion can get messy.

A further consideration is that TIMESTAMP also acquires some subtle timezone conversions which get rather messed up if you move to maxdb mode or back. Indeed, datetime data will be more portable across different systems.

symcbean
  • 47,736
  • 6
  • 59
  • 94
-1

It does not matter as long as you are using UTC value. However, timestamps are better way to store data from different timezones as it always represents and stored as the standard time(UTC) irrespective of the timezone of client/server.

But, as you are taking the data from user's input it does not make a difference.

For the accurate standard time(UTC), you just need to convert it right according to the user's timezone.

You can retrieve user's timezone either from browser headers(which is a less accurate method) or you can ask the user himself(using a input).

Parantap Parashar
  • 1,930
  • 1
  • 14
  • 22
  • 1
    No, its always stored as UTC relative to the configured timezone on the server. – symcbean Feb 24 '17 at 22:48
  • Yes. And that is what I am trying to say. Maybe you doesn't understood the answer right. It will not do much in case of user input as user may be of different timezone than that of the server. – Parantap Parashar Feb 27 '17 at 06:23