CODEWITHSUNDEEP
phpmysqldatetime
Rashid Farooq
Rashid Farooq

Reputation: 119

How to Ensure the Correct Sorting on the Base of Date and Time in MySQL

I am in a great confusion now. I have a comments table in mysql database. in comments table there is a field comment_posted_time (type DATETIME), I sort all the comments like this

$query = "SELECT * FROM comments ORDER BY comment_posted_time DESC";

Now, This can produce wrong results. I give you an example. Suppose there are two users sitting in front of computer, one in India and the other in America. Suppose India's Time is 10 hours forward from America. first the user from India posts a comment and its current local time is stored in comment_posted_time (type DATETIME). After 1 hour the user from America posts a comment and its current local time is stored in comment_posted_time (type DATETIME) as well. Now, in database the later posted comment from American User will not be counted the most current comment and sorting will not be correct.

How to handle this situation?

Upvotes: 0

Views: 89

Answers (4)

Vahe Shadunts
Vahe Shadunts

Reputation: 1966

There is no difference where are your users, your php and mysql are in one server, so you must not have a problem with times, You can show times for each user by his timezone converting it by php, but don't change mysql default timezone for each user. Design your comments table and set on comment_posted_time default value NOW(), and don't use this field in your insert queries. So all records in that field will saved in same timezone.

Upvotes: 1

sics
sics

Reputation: 1318

So if you're facing the problem, that your users are in different timezones you should ALWAYS calculate timepoints to one given timezone bofore persisting it (usually you take the main servers timezone, or UTC per default). With this approach you can do both: order by timepoint and show users the timepoint calculated to their timezone.

Upvotes: 0

Aiias
Aiias

Reputation: 4748

You can convert the date to its UNIX_TIMESTAMP and then sort by that.

$query = "SELECT * FROM comments ORDER BY UNIX_TIMESTAMP(comment_posted_time) DESC";

You can read more about UNIX_TIMESTAMP() documentation.

Edit:

You should not be storing the comment_posted_time relative to different timezones. All of the times should be relative to one timezone, like GMT, or the machine running php.

Upvotes: 0

Adam Plocher
Adam Plocher

Reputation: 14233

How are getting the date for comment_posted_time? Typically this would be the current date on the MySQL server. If you're getting the datetime from the client machine (which I would NOT recommend), you could get the UTC date/time and then convert it back to the local time when you display it.

To store the current time from the MySQL server, you can do something like:

update comments set comment_posted_time = NOW() where id=...

This will not differ based on the client...

Upvotes: 0

Related Questions