Time offset when pulling time from DB [SOLVED x2]

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Time offset when pulling time from DB [SOLVED x2]

Post 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
Last edited by Stryks on Tue Feb 27, 2007 7:27 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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)?
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Storing in time stamp form, is often preferred for many reasons. ;)
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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()
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

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