Issue in timestamp in MySQL

Question

This is my table

[ID] [TIMESTAMP]
[1] [2013-12-19 01:17:35] []
[2] [2013-12-19 01:23:42] []

the time which is being shown here is different from my timezone. Lets say the time it is showing is 2013-12-19 01:17:35 but in my timezone the time is 2013-12-19 13:47:35.

My issue is that i want to fetch the time of my timezone not the default one.

Can someone help me on this?

score 1 · Answer 1 · answered Dec 20 '13 at 05:48

1

It store in UTC, you can convert on receiving back to your local timezone

date_default_timezone_set('Asia/Tokyo'); // your reference timezone here

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

$date = /* SELECT date FROM database */;

$usersTimezone = new DateTimeZone('America/Vancouver');
$l10nDate = new DateTime($date);
$l10nDate->setTimeZone($usersTimezone);
echo $l10nDate->format('Y-m-d H:i:s');

answered Dec 20 '13 at 05:48

Zaheer Ahmed

28,160
11
74
110

can I change the timestamp in MySQL? or I can do this only in php? – Ankush Dec 20 '13 at 05:50

score 1 · Accepted Answer · edited May 23 '17 at 12:22

1

You need change the timeszone in you MySQL. Just write query on you root file( may be index.php ) change the timestamp in MySQL. For tokyo

query("SET time_zone = 'Asia/Tokyo'");  //set your exact location

Edit make sure where you are? see wikipedia For more details to how set timezone? see how to set timezone of mysql?

edited May 23 '17 at 12:22

Community

1
1

answered Dec 20 '13 at 09:06

Imran

3,031
4
25
41

Issue in timestamp in MySQL

2 Answers2