SET time_zone problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

SET time_zone problem

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: SET time_zone problem

Post 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?
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Re: SET time_zone problem

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: SET time_zone problem

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SET time_zone problem

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: SET time_zone problem

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SET time_zone problem

Post by Eran »

Yes, you are right. I meant using native time functions like NOW() and CURTIME()
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Re: SET time_zone problem

Post by itsmani1 »

Weirdan


The question still stands: how did you check the inserted time?
By going into to database see values in tables.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: SET time_zone problem

Post 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.
Post Reply