| 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.
| 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?
| 12:41 pm on Oct 5, 2011 (gmt 0)|
Use gmDate instead.
| 1:14 pm on Oct 5, 2011 (gmt 0)|
$datetime = date('Y-m-d H:i:s');
$datetime = gmDate('Y-m-d H:i:s');
but it is still showing as 60 minutes out hmmmmmm
| 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.
| 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.
| 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?
| 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.
| 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.
| 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.
| 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.