Adjust "NOW()" timezone

Question

Right now my query looks like this (relevant part):

UPDATE last_seen=NOW()

But it inserts a time which is 2 hours earlier than mine. Is there any way to adjust it?

Thanks

@w0rldart Yes, but obviously i need to adjust it in this case :) — Johan, Jul 05 '12 at 20:56
@Johan, I've given you a detailed answer with different ways to fix your problem... hope you find it useful — Alex, Jul 05 '12 at 21:04

score 2 · Accepted Answer · edited May 23 '17 at 12:20

2

Set the timezone on your DB.. take a look this post. https://stackoverflow.com/a/3451971/1227435

This is another great resource:

Having Timezone problems with PHP and MySQL

edited May 23 '17 at 12:20

Community

1
1

answered Jul 05 '12 at 20:53

Roberto Navarro

948
4
16

Assuming your using a mySQL DB. What DB are you using? – Roberto Navarro Jul 05 '12 at 20:54
Yea its mySQL, sorry for not mentioning that. So i just pass the timezone at the end of my query? – Johan Jul 05 '12 at 20:56
Did you get it? Look @ UTC_TIMESTAMP() – Roberto Navarro Jul 05 '12 at 21:02

score 1 · Answer 2 · answered Jul 05 '12 at 21:00

I recommend you to try something like so:

Have an hidden input to collect user's timezone

<input type="hidden" name="offset" class="offset">
$('.offset').val(new Date().getTimezoneOffset());

Then get it before the update and convert it to seconds like so

$offset    = intval($_REQUEST['_offset']) * 60;

Finally add it to time():

$now = time() + $offset;

Doing the above, you have a variable with the client's time in his timezone. If you don't want that, and want a common time for everybody just set your time zone with this in your index.php:

date_default_timezone_set('TIMEZONE IN HERE'); //Supported timezone list: http://www.php.net/manual/en/timezones.php

But it's better if you set it in your php.ini (the timezone) or changing server's timezone (Debian/Ubuntu: dpkg-reconfigure tzdata)

Jason McCreary · Answer 3 · 2012-07-05T21:02:10.183

0

Sounds like MySQL is not on the same timezone as you.

You have some options:

Set the timezone for MySQL.
Use PHP to set the date before storing.
Store as a UTC timestamp and then display correct local time. More code, but I recommend.

edited Jul 05 '12 at 21:02

answered Jul 05 '12 at 20:54

Jason McCreary

71,546
23
135
174

What would be a valid date format for a `timestamp`, if using php date function? – Johan Jul 05 '12 at 20:58

score 0 · Answer 4 · answered Jul 05 '12 at 20:55

0

http://dev.mysql.com/doc/refman//5.5/en/time-zone-support.html you need to set MySQL's notion` of timezone. Hope that helps...

answered Jul 05 '12 at 20:55

hd1

33,938
5
80
91

Adjust "NOW()" timezone

4 Answers4