0

My web application & mysql DB hosted in US server, so when I want to store current date and time, it storing in US time, because I use NOW() function of mysql, but I want to store current date time in Indian Standard time. How can be possible in mysql or php? What would be the effective way to achieve this?

Able Alias
  • 3,824
  • 11
  • 58
  • 87

2 Answers2

2

There are several possibilities.

1) Change mysql ini setting time-zone to "IST"

2) You can convert time zones using tz_convert()

Convert dates from one time zone to another by using somthing like this

SELECT CONVERT_TZ(Now(),'GMT','IST');

For this to work you have to have a valid time zone configuration, see http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html for details.

If time zones are not set up properly you may still convert Now() to your local time zone if you specify the time offsets of server time zone and your desired time zone, e.g.

SELECT CONVERT_TZ(Now(),'+00:00','+08:00');

3) If your mysql user has the super privilege you can simply set the time zone at runtime using

SET time_zone = 'IST';

Further documentation can be found in the time zone manual section of mysql http://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html

maxhb
  • 8,554
  • 9
  • 29
  • 53
  • actually this helps me CONVERT_TZ(NOW(),'-07:00','+05:30') – Able Alias Mar 11 '16 at 11:33
  • Glad to hear that you found a solution. If you feel that my answer solved you're problem it would be good if you accept it as a valid answer, see http://stackoverflow.com/help/accepted-answer – maxhb Mar 11 '16 at 12:28
2

Why don't you use PHP date and timeZone functions to get current Indian timestamp?

date_default_timezone_set("Asia/Kolkata");

$curr_timestamp = date("Y-m-d H:i:s");

Sorry if I got you wrong but please let me know if that works for you.

sravs
  • 330
  • 2
  • 14
  • Hi, did this helped u? – sravs Mar 11 '16 at 10:48
  • your answer working fine but if website have so many user with many other country, then what we can do ? We should be write all `date_default_timezone_set('.....');` for all user country ? – AlwaysStudent Apr 27 '18 at 14:44