PHP date/time problem when inserting into database

Time is out by 60 minutes

[dowzer]

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]

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]

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

Thanks

Jason

[g1smd]

Use gmDate instead.

[dowzer]

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]

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]

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]

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]

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]

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]

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

[enigma1]

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.