Page 1 of 1

Time offset when pulling time from DB [SOLVED x2]

Posted: Tue Feb 27, 2007 4:44 pm
by Stryks
Hey all,

I always have troubles with times and dates when transferring from mySQL to PHP, but I thought I'd sit down and nut it out.

I feel like I'm so close ... but no dice.

Ok, so I'm writing the current date/time to a DATETIME field in the db with

Code: Select all

putenv('TZ=Etc/GMT');
	$db_time = date('YmdHis');
	$sql = "UPDATE `tbl_admin_user` SET `last_log` = '$db_time' WHERE `U_PK` = '$u_id '";
The time format used to be 'Y-m-d H:i:s' but this works just the same.

All good at this point, the database shows the same value of $db_time.

I pull the value back with:

Code: Select all

$sql = "SELECT `U_PK` , `username` , `auth_level`, `last_log`, UNIX_TIMESTAMP(last_log) AS `last_log_ts` FROM `tbl_admin_user` ORDER BY `username` ASC";
Thats seems ok I guess. `last_log` seems identical to the db value, and `last_log_ts` is at least a timestamp value.

But here is the problem. If I use:

Code: Select all

echo $user['last_log'] . " - " . date('Y-m-d H:i:s', $data['last_log_ts']);
The values are close, but different.
2007-02-27 21:55:16 - 2007-02-27 10:55:16
Any clues for me on this one ... I've been staring at this for far too long.

Cheers

Posted: Tue Feb 27, 2007 5:06 pm
by feyd
In all probability, it's a confusion on MySQL's end of things. Specifically, it is processing the date as a local time stamp, not a GMT one.

What time zone is your server located (and configured for)?

Posted: Tue Feb 27, 2007 5:16 pm
by Stryks
The production server is GMT but my testing server is ... well ... unknown.

I'm not really sure why mySQL would be having a problem with the conversion though. I mean ... it shouldn't really be doing any conversion at all should it?

I mean ... I put the time in in the timezone I wanted it. Surely all the UNIX_TIMESTAMP function should be doing is translating a given date / time into a timestamp.

I'm going to put it up on production to give it a test. Will let you know the result.

If this does in fact turn out to be the problem, is there a good clean way of changing the raw datetime into a timestamp in PHP?

Posted: Tue Feb 27, 2007 5:24 pm
by feyd
Storing in time stamp form, is often preferred for many reasons. ;)

Posted: Tue Feb 27, 2007 5:39 pm
by Stryks
Well .. production server runs it like a champ.
2007-02-27 23:25:44 - 2007-02-27 23:25:44
It's funny what you say about timestamps, because when I was gearing up to sort this out for once and for all, I did alot of searching aroun in these forums, and it really seemed that opinion was divided.

Half seemed to think that datetimes were better because you could use date comparisons in queries (to pull say, entries from the past 30 days) and they would also store dates older then timestamps will allow.

The others were of the "store the PHP timestamp and let mySQL go screw itself' persuasion.

But I went with datetime, and this code is actually part of a replacement for storing timestamps.
:roll:

But OK.

To store timestamps, I just do the GMT conversion, get the date() (because time() always seems to return local time) and save into a straight int(11)?

If I store as a timestamp, can I still do mysql range calls? Perhaps with the functions I cant recall right now. Ummm ... FROM_TIMESTAMP maybe, and use an INTERVAL query as usual? Or maybe just take (30 x 86400) from current timestamp and bring back timestamps >=

Any tips on a direction here would be great. I'm happy to use timestamps ... I'm not going to be storing old dates.

Thanks a bunch

Posted: Tue Feb 27, 2007 6:10 pm
by feyd
MySQL has no problem switching between both and you can still do everything you could with date-time fields, you just need to add FROM_UNIXTIME() into the mixer.

Posted: Tue Feb 27, 2007 6:59 pm
by Chris Corbyn
To set the timezone in your MySQL session:

Code: Select all

SET time_zone="+05:00"
In that notation. Times are offset from UTC.

EDIT | That obviously remains changed until you can mysql_close()

Posted: Tue Feb 27, 2007 7:26 pm
by Stryks
Awesome. Thanks for that.

I have just modified my DB class to send that just after connection. (all my times are going to be stored at GMT)

Now it doesn't depend on server config for correct operation.

Thanks again guys for all the help :D