0

I am storing the user viewdatetime column from my server time (India), and I want to convert this time depending on the users country. I can get the user country and some details by this steps Getting visitors country from their IP

Can you give me some suggestion

Community
  • 1
  • 1
Shaji Jesus
  • 11
  • 2
    Possible duplicate of [Convert UTC datetime to another timezone - PHP](http://stackoverflow.com/questions/11883757/convert-utc-datetime-to-another-timezone-php) – HPierce Sep 14 '16 at 00:31

2 Answers2

0

The best way to do it would be using moment.js library (http://momentjs.com/) and benefit from its Multiple Locale Support. You should really handle this stuff on client-side (thus JavaScript) instead of server-side (PHP). I hope this answer will be useful.

Alberto Schiabel
  • 1,049
  • 10
  • 23
0

I guess your viewdatetime column is a DATETIME datatype. If that's true, then the CONVERT_TZ() function will prove helpful. For example, if your user's time zone is Australia/Canberra you can do this:

 SELECT CONVERT_TZ(viewdatetime, 'Asia/Dili', 'Australia/Canberra');

But, of course you have to use a public API that guesses the user's time zone from the ip address. Knowing the country isn't enough.

India has a single national time zone. Australia has four. USA has a chaotic set of local rules about daylight savings time, meaning there are roughly twelve distinct time zones. Canada has six zones.

Best practice in this area is to allow each registered user to specify her own time zone preference, and show unregistered users dates and times in your own time zone.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • wooow, really you are very great, thank you very much.Did you like this project? Isn't any possible to choose automatically timezone? But facebook and many websites not asking to choose time zone. – Shaji Jesus Sep 13 '16 at 19:11
  • Facebook knows your city of residence because they insist you tell them. They derive timezone from that. – O. Jones Sep 13 '16 at 22:44
  • I my db I am storing the data using "+05:30", and how can I convert it to users time zone like "+05:00" time – Shaji Jesus Sep 15 '16 at 05:06
  • You wrote "storing the data." Do you mean "storing the timezone offset?" If so, `+05:30` is a poor way of doing that. It doesn't deal correctly with seasonal ("daylight savings time") changes in timezone offset. If you use the MySQL `CONVERT_TZ()` function you will get the advantage of using the worldwide zoneinfo project. https://en.wikipedia.org/wiki/Tz_database – O. Jones Sep 15 '16 at 11:04