homepage Welcome to WebmasterWorld Guest from 107.22.70.215
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe and Support WebmasterWorld
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
PHP date/time problem when inserting into database
Time is out by 60 minutes
dowzer




msg:4370773
 10:02 pm on Oct 4, 2011 (gmt 0)

I am PHP and MySQL for a simple CRM type system and I have an audit database which tracks inserts, deletes, changes and so on.

In that database I capture the date of the event - whenever any signficant action occurs I use a variant of the below:


#Auditing
$company_id = $_SESSION['company_id'];
$username = $_SESSION['username'];
$user_ip = $_SERVER['REMOTE_ADDR'];
$datetime = date('Y-m-d H:i:s');
$action = "Updated company information";
$sql = mysql_query("INSERT INTO auditing values ('','$company_id','$username','$user_ip','$datetime','$action')");


The information is all entered but the time is entered as 60 minutes behind the correct time - I have checked the time on the server and it is correct and if I do something like this in PHP:


$now=date('Y-m-d H:i:s');
print $now;


it also returns the correct date BUT as soon as I login it enters the datetime as 60 minutes behind the actual time. If I login at 22:58:13, it enters it as 21:58:14

I have looked through all my scripts and I am not modifying the date anywhere and I just cannot figure out why this is happening.

What am I overlooking? :(

Jason

 

g1smd




msg:4371007
 12:02 pm on Oct 5, 2011 (gmt 0)

If you're on DST, perhaps it is entering it in your standard time.

Consider this scenario if it uses the local clock time. You enter something at 1:59 am at the end of October, and then then one minute later the clocks go back from DST (2.00 a.m.) to standard time (1.00 a.m.). Another minute later you enter something and it is stored as 1.01 a.m.

If you now sort by date/time, the newer item will show as being 58 minutes before the older one (when it should be 2 minutes after).

You should normalise all dates and times to UTC before you store them in your database. The viewer can then select their offset from UTC to see the data in their local timezone.

dowzer




msg:4371016
 12:32 pm on Oct 5, 2011 (gmt 0)

How can I check if this is the case and how can I correct it, if it is?

Thanks

Jason

g1smd




msg:4371018
 12:41 pm on Oct 5, 2011 (gmt 0)

Use gmDate instead.

dowzer




msg:4371027
 1:14 pm on Oct 5, 2011 (gmt 0)

I changed:

$datetime = date('Y-m-d H:i:s');

to:

$datetime = gmDate('Y-m-d H:i:s');

but it is still showing as 60 minutes out hmmmmmm

penders




msg:4371028
 1:16 pm on Oct 5, 2011 (gmt 0)

This certainly sounds like a Daylight Saving Time (DST) issue. date() returns the local (server) time, taking into account DST. In the UK this is currently GMT+1. You later appear to be fetching GMT (60 mins behind) from your database.

g1smd




msg:4371043
 2:11 pm on Oct 5, 2011 (gmt 0)

By using gmDate the date/time will be consistently nailed to UTC all year round. You then need to ask the user which time zone and offset they want to display that data for.

If you use something other than gmDate your data timeline will have a +1 hour step change at the onset of summer and a -1 hour step change (and resultant timeline corruption) at the onset of winter.

dowzer




msg:4371084
 3:30 pm on Oct 5, 2011 (gmt 0)

Thanks G1 - doesn't sound what I want to do especially as this is for the audit database so I should be deciding the format and not the user.

Any other ideas what could cause this? Am totally baffled especially as echoing the string in a script shows the right time?

rocknbil




msg:4371106
 3:58 pm on Oct 5, 2011 (gmt 0)

Why can't you do this?

$sql = mysql_query("INSERT INTO auditing values ('','$company_id','$username','$user_ip',now(),'$action')");

... Using the mysql date and time function for that purpose.

It's entirely possible the server and mysql server are, for whatever reason, out of sync.

enigma1




msg:4371113
 4:08 pm on Oct 5, 2011 (gmt 0)

It's entirely possible the server and mysql server are, for whatever reason, out of sync.

What I have seen and it's becoming popular lately is that the mysql server is at a different geolocation than the main server.

So best not to use the mysql 'now()' but instead use the main server's time date functions when writing records in the database.

dowzer




msg:4371141
 4:53 pm on Oct 5, 2011 (gmt 0)

MySQL is on the same box - when I check in MySQL using SELECT NOW(); it returns the correct date and time.

enigma1




msg:4371856
 11:31 am on Oct 7, 2011 (gmt 0)

There are 3 different times that need to be in sync. The server time, the php time and mysql time. If the timezones aren't setup right or aren't setup at all I don't know what will happen with the time conversions. So to be on the safe side and since the application you run is in php, use the php time functions. Replace the the 'now()' with date('Y-m-d H:i:s') when you insert or update db records and retest.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved