MySQL system time and Apache time are different

[midnighthearts]

There is a problem with apps counting on time as a parameter.
The system time in MySQL differs from Apache's one.
#1 The difference is close to an hour (it could be expected for a time shift zone or summer/winter time shift not followed by one of the subsystems)
#2 Since the difference isn't exactly an hour there is serious need to think about ntp synchronization.
#3 How come it happens if subsystems are on the same box ?

If you know, what would be a programmer's best practice to align the times? i.e. replacement for PHP time() or for MySQL CURRENT TIME?

 

[Smith6612]

MySQL and the web server I'm almost positive are on separate servers. Might just be possible that due to the recent change in Daylight Savings one of the servers failed to roll their clock back. I came across a few PCs today which failed to do the same thing (with NTP disabled on them as well). Just to point out, one of my older PCs also rolled it's clock back a week early oddly enough.

As for the programming, someone else can help you. I'm still not that well versed with PHP yet.

 

[garrettroyce]

The MySQL and Apache are not on the same server anymore. PHP could just be changing the time for whatever the set TZ is for the server, whilst MySQL is just taking the server time off of the server.

As long as you take the time from the same place all the time, I would say that is the best practice. I personally would use PHP's datetime functions, because I know how they work, but I'm sure MySQL's time functions are alright too.

@Smith6612 I think the official time committee or whatever changed when the clocks roll back every year, so devices not set to keep up with this change don't know about it.

 

[midnighthearts]

Let's assume I'll update the time fields in MySQL with values out of PHP. MySQL has some fields set up for automatic fill in, i.e. by the time a record is created or updated. Will the two approaches collide and which rule wins in MySQL?

 

[garrettroyce]

If you use the MySQL date functions, they will use MySQL's time. But, if you want to be consistent throughout your program, I would only get time from one source, PHP being the easier of the two so you don't have to query for time. You might also want to consider the possibility that you'll want the time zone on your site to change to UTC or your local time instead of X10's local time, then you'd have to update every MySQL query anyways to shift TZs for the database. However, the MySQL CURRENT_TIMESTAMP() is very helpful and losing it is annoying.

I don't know why this is happening, and if you want I can try to have someone look into it, but I think the fact that it is happening should be reason enough to make sure your code is safe against it

 

[midnighthearts]

I don't know why this is happening, and if you want I can try to have someone look into it, but I think the fact that it is happening should be reason enough to make sure your code is safe against it

What particular part are you willing to look on? I would appreciate the fix for the time on the server side. I'm in rapid prototyping mode and don't want to spend much time on rewriting the code that has been working. Later, if the projects proves to be worthy the effort I'll take care of this code vulnerability.

Point on code best practice. There is an opinion that the most time stamping should be handled by the storage. It is a good approach if the system will grow one day and multiple clients will connect to DB. This way you can be sure the clients speak on the common time ground and are not cheating on each other.

 

[garrettroyce]

Point on code best practice. There is an opinion that the most time stamping should be handled by the storage. It is a good approach if the system will grow one day and multiple clients will connect to DB. This way you can be sure the clients speak on the common time ground and are not cheating on each other.

I can agree on that. I suppose it's whether you want accuracy or speed. There's a limited range of scenarios where the database must be 100% accurate on time, but if PHP has to query the database for a time, that could slow execution down for every page load. Both are a bit nit-picky for my taste, but I think you have a very good idea of good coding so you can make that decision when you get to it

I will escalate this issue to staff attention so that they may be able to fix it if it doesn't fix itself. You should have a reply within a few hours.