0

I'm using the timestamp data type in MySQL so that any timestamp saved will be converted into GMT, then back from GMT on retrieval. From the documentation, it seems like this is all automatically done for me by MySQL; all I need to do is use the timestamp data type.

However, I noticed that it doesn't seem to work. I've tested this on my local machine and server -- and it seems that it always saves the local time instead of GMT. The local machine and server are in different time zones and neither are in GMT.

Here's my test insert:

INSERT INTO `test` (`test_id`, `stamp`) VALUES (NULL, CURRENT_TIMESTAMP);

Any ideas what I'm doing wrong?

StackOverflowNewbie
  • 39,403
  • 111
  • 277
  • 441
  • what mysql browser tool your are using? phpmyadmin? or mysql terminal? – Netorica May 13 '12 at 07:50
  • yes... i been stucked with the same problem... but when i tried browsing my database to mysql terminal and setting my mysql with this code "SET time_zone = '+00:00';" it i saw my data is save in GMT – Netorica May 13 '12 at 07:55
  • I tried it in HeidiSQL, but it was still storing it in local time. Then I preceded the insert SQL with the set time zone. Still saving in local time. – StackOverflowNewbie May 13 '12 at 07:58
  • try it on your mysql terminal... use command prompt to access your mysql if your using windows... – Netorica May 13 '12 at 07:59
  • I'm not familiar how to use MySQL from command line. Since it's my app who will be doing this, I can do it from there instead? – StackOverflowNewbie May 13 '12 at 08:11
  • hmm try to include the set timezone code i answered below in every query you will be doing. – Netorica May 13 '12 at 08:12

1 Answers1

4

try to access your database using mysql terminal

1) first enter this code SET time_zone = '+00:00';

2) then now do your SELECT to check your data

You can access your mysql by using the follwing the method in this link. How can I access the MySQL command line with XAMPP for Windows?

I hope you would understand it in your case

NOTE:

timestamps automatically converted in the timezone adjusted for any client connected on mysql. so you can't really see its original time but it always depends on your set timezone

Community
  • 1
  • 1
Netorica
  • 18,523
  • 17
  • 73
  • 108
  • I don't know how to use MySQL from command line. Testing this from my app. – StackOverflowNewbie May 13 '12 at 08:14
  • timestamps automatically converted in the timezone adjusted for any client connected on mysql. so you can't really see its original time but it always depends on your set timezone...im still thinking where you went wrong – Netorica May 13 '12 at 08:29