php code, timezones affecting mysql?

[Troy Piggins]

We have some php pages on our intranet at work that have been working
fine for years. As a result, they rarely get edited.

The page I'm having trouble with is a timesheet entry page which
uses timestamps to record hours spent on different projects in a
mysql database. If users try to enter times using the php
interface, they are getting recorded correctly in the database
because I can directly access it and see the entries there.

However the php page that is supposed to display the hours by
reading back the entries from the database is /not/ correctly
displaying all the entries. It's never done this before.

Around the time that the page started misbehaving I noticed that
the clock on the server was ahead of our timezone by some 7 hours
or so. That was fixed as soon as I noticed it. For some reason
the ntp client didn't update.

I'm wondering if the system clock issue had something to do with
it? Any ideas on where to start the forensics?

I haven't posted any code at this stage to remain concise, but if
needed I can post code and sample outputs.

--
Troy Piggins

[Gordon Burditt]

>We have some php pages on our intranet at work that have been working

>fine for years. As a result, they rarely get edited.

The page I'm having trouble with is a timesheet entry page which
uses timestamps to record hours spent on different projects in a
mysql database. If users try to enter times using the php
interface, they are getting recorded correctly in the database
because I can directly access it and see the entries there.

Beware of subtracting times from different clocks. This can result
in problems if one of them is significantly incorrect.

>However the php page that is supposed to display the hours by
reading back the entries from the database is /not/ correctly
displaying all the entries. It's never done this before.

What is the problem? (a) Some entries are missing, (b) the hours aren't
correctly computed, or (c) something else?

You've got potentially three different clocks here: (a) The MySQL
server clock, (b) the PHP server clock, and (c) the clock on the
wrist of the guy entering the times. They are probably all incorrect
a little (milliseconds), and possibly by a lot (hours, days, or
years). All of these might have different time zones also.

Nasty things can happen if, for example, the data is taken from the
times the user enters (clock (c)), but you only show entries that
are in the past according to the MySQL SERVER clock(clock (a)).
You might always be missing the latest couple of hours of entries.

If you're displaying a task time for a still-running task (started
but not yet stopped) which clock was used for NOW to calculate
hours-so-far? Which clock was used to calculate the start time?
If you're mixing now() in MySQL with time() in PHP, that may be the
source of the problem.

>Around the time that the page started misbehaving I noticed that
the clock on the server was ahead of our timezone by some 7 hours
or so. That was fixed as soon as I noticed it. For some reason
the ntp client didn't update.

Is the problem now fixed, for entries made entirely after the clock
was reset?

>I'm wondering if the system clock issue had something to do with
it? Any ideas on where to start the forensics?

You've got several clocks. Which are you using?

>I haven't posted any code at this stage to remain concise, but if
needed I can post code and sample outputs.

[Troy Piggins]

* Gordon Burditt is quoted
* & my replies are inline below :

>>We have some php pages on our intranet at work that have been working
fine for years. As a result, they rarely get edited.

The page I'm having trouble with is a timesheet entry page which
uses timestamps to record hours spent on different projects in a
mysql database. If users try to enter times using the php
interface, they are getting recorded correctly in the database
because I can directly access it and see the entries there.

Beware of subtracting times from different clocks. This can result
in problems if one of them is significantly incorrect.

>>However the php page that is supposed to display the hours by
reading back the entries from the database is /not/ correctly
displaying all the entries. It's never done this before.

What is the problem? (a) Some entries are missing, (b) the hours aren't
correctly computed, or (c) something else?

Sorry I omitted that. Some entries are missing, just displaying
on the page as though there is no entry at all. Some entries are
displaying the correct no of hours in the correct day.

You've got potentially three different clocks here: (a) The MySQL
server clock, (b) the PHP server clock, and (c) the clock on the
wrist of the guy entering the times. They are probably all incorrect
a little (milliseconds), and possibly by a lot (hours, days, or
years). All of these might have different time zones also.

c) above is not an issue. The actual times are not entered, just
durations. So they would just enter that they worked on XYZ
project for 4 hours on 22/11/07.

I hadn't thought about the mysql server clock. Do you mean
mysqld has it's own time keeping mechanism? Or do you mean the
machine that mysqld is running on? Both the apache/php and
mysqld servers are the same machine.

I corrected the time on the machine by using the 'ntpdate' command
as root IIRC.

Nasty things can happen if, for example, the data is taken from the
times the user enters (clock (c)), but you only show entries that
are in the past according to the MySQL SERVER clock(clock (a)).
You might always be missing the latest couple of hours of entries.

If you're displaying a task time for a still-running task (started
but not yet stopped) which clock was used for NOW to calculate
hours-so-far? Which clock was used to calculate the start time?
If you're mixing now() in MySQL with time() in PHP, that may be the
source of the problem.

I understand, but as mentioned above it's not that sofisticated.

>>Around the time that the page started misbehaving I noticed that
the clock on the server was ahead of our timezone by some 7 hours
or so. That was fixed as soon as I noticed it. For some reason
the ntp client didn't update.

Is the problem now fixed, for entries made entirely after the clock
was reset?

Well, the problem persists for some entries made around that time
- maybe a week or 2 before the problem was pointed out to me.
Some of the guys enter their timesheets directly into the
timesheet php interface daily, while others write it in a diary
and just add it when they need to submit their timesheets.
That's why I didn't notice it sooner.

I have just tested some for this current week and it seems to be
working ok again.

Just curious about the reason behind it so it doesn't happen
again.

>>I'm wondering if the system clock issue had something to do with
it? Any ideas on where to start the forensics?

You've got several clocks. Which are you using?

Hopefully this is answered above.

Thanks for assisting.

--
Troy Piggins