Server time, not local user's time (php, mySQL)

A quick question:

Timezones around the world... users in different timezones.

I need to compare when a record was updated, not based on local time, but on server time.

I have a datetime field in mySql, default is: 0000-00-00 00:00:00       

I have a form, and that form processing should update a field in a record. If someone in London and someone in Chicago update their record nearly simultaneously, the database should show the times as whatever was correct at the server at the time, not local time. Even though Peter and Paul are 6 hours apart in time zones, if they update nearly simultaneously, I would want:

 2012-03-24 23:49:03       Peter, London
 2012-03-24 23:49:00       Paul, Chicago

Will the php function Now() update the record with server time, or local time? If local, then what function do I need to use, instead.

Thanks!

Dennis
dtleahyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
That's a MySQL function, not a PHP function, and it uses the Server time.  http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_now
0
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
And actually, PHP Date and time functions will use the server time unless you have changed the timezone.  Without you telling it, it doesn't even know the users time zone.
0
 
dtleahyAuthor Commented:
Thanks Dave, for the quick reply,

So...
(from the php file)
$sql="UPDATE mytable SET zdatetime=Now() WHERE (zuserid = $row[vuserid ])";	

Open in new window


Simple as this?

Do I need to format it at all? I don't need to display the data in the zdatetime field, I just need to make a comparison, so I can (for example) query to get the last 10 people who have activated.

Dennis
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
That's what I would do except it's NOW(), not Now().  Then you can do:

$qlast10 = "SELECT * FROM mytable ORDER BY zdatetime DESC LIMIT 10";

to get the latest 10 entries.
0
 
Ray PaseurConnect With a Mentor Commented:
This article will give you some background on the use of timezones in PHP and MySQL.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Executive summary: PHP tries to be timezone sensitive.  See the output of date('c') to understand its use of UTC offset.  But MySQL does not carry a timezone offset in the data type DATETIME.  So one strategy is to set your MySQL clock to UTC and use date_default_timezone_set() in PHP.  That will keep your MySQL data base consistent and you can still have the option to show local times to clients.
0
 
dtleahyAuthor Commented:
Thanks, Ray!

Excellent info at that link! Thank you so much for taking the time (a few years ago) to amass that accumulated knowledge into what is a whitepaper. Great resource!

In my current programming situation, the only thing this datetime field will be used for is to do an ORDER BY in the SQL statement. I'll never display the field data and the people around the world will never need to know the offset from GMT, or do any datetime math for example.

This has nothing to do with auctions, but as a good example, imagine an item being auctioned off and people around the world are bidding. I would need to know what time they hit the server, not what time it was at their particular locality.

The exact reason I want this is so that I can display a list of the most recent 10 people that have signed-up to a website I'm building, (no matter where they are in the world.)

Dennis
0
 
dtleahyAuthor Commented:
Thanks, Dave and Ray! You guys are gems!<br />-Dennis
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.