Page 1 of 1

SET time_zone problem

Posted: Mon May 31, 2010 3:24 am
by itsmani1
Hi,

I am facing a problem in SET time_zone

Code: Select all

mysql_query("SET time_zone = '+4:00'") or die("");
mysql_query("insert into `aa` set mytim = now()") or die("");
I expect this to insert time GMT +4 time in database but it does not do that. how can i do that? i have field of "timestamp" type and i want to insert FMT +4 time in it when updated.

any help please

thanks

Re: SET time_zone problem

Posted: Mon May 31, 2010 3:52 am
by Weirdan
I expect this to insert time GMT +4 time in database but it does not do that.
How do you know it doesn't?

Re: SET time_zone problem

Posted: Mon May 31, 2010 4:39 am
by itsmani1
I tried to insert time but i noticed that it inserted GMT +5 instead of GMT +4

mysql time set on my server is GMT+5 it's shared server and i want to set GMT +4 for my database only.

Re: SET time_zone problem

Posted: Mon May 31, 2010 5:56 am
by Weirdan
itsmani1 wrote:I tried to insert time but i noticed that it inserted GMT +5 instead of GMT +4
The question still stands: how did you check the inserted time?

Re: SET time_zone problem

Posted: Mon May 31, 2010 9:04 am
by Eran
Timestamps are saved internally as UTC (GMT +0). The only thing that changes is the select values depending on the time zone you set. Issuing a SET time_zone query before you insert is meaningless, SET time_zone is per connection and inserts don't need timezone. You can set the global timezone using SET GLOBAL time_zone. Personally, I always fetch the unix timestamp (using UNIX_TIMESTAMP()) and allow PHP native timezone aware functions to take care of it.

http://dev.mysql.com/doc/refman/5.1/en/ ... pport.html

Re: SET time_zone problem

Posted: Mon May 31, 2010 9:46 am
by Weirdan
pytrin wrote:SET time_zone is per connection and inserts don't need timezone.
This really depends on where the value for insert is coming from. If it's coming from some internal function (like itsmani1 is using) - then you're right and connection timezone won't affect the insert. However if you pass the value literally in the query string then it would be converted to UTC according to the current connection timezone. And if you fail to set it properly, it won't be converted properly.

Re: SET time_zone problem

Posted: Mon May 31, 2010 10:01 am
by Eran
Yes, you are right. I meant using native time functions like NOW() and CURTIME()

Re: SET time_zone problem

Posted: Tue Jun 01, 2010 12:48 am
by itsmani1
Weirdan


The question still stands: how did you check the inserted time?
By going into to database see values in tables.

Re: SET time_zone problem

Posted: Tue Jun 01, 2010 5:22 am
by Weirdan
itsmani1 wrote:By going into to database see values in tables.
'Going into database' means connecting to it right? And it's where your per-connection (of the tool you use for checking) timezone kicks in. Thus timestamps are converted to requester's timezone.