CODEWITHSUNDEEP
mysqlsql
LASH
LASH

Reputation: 70

Sorting by time not working truly in MySql

I think title is clear what is my problem. I am working with MySql I just wanted to know what was the first registration on my website and when I sorted the table according to timestamp, the id of people who registered on the website were not true. The Image bellow will show what do I mean: phpMyAdmin

and this is the code what phpMyAdmin created:

SELECT *
FROM `table`
ORDER BY `table`.`timestamp` ASC
LIMIT 0 , 30

whats wrong with my table time? and why id is not ordered same as timestamps

Upvotes: 0

Views: 39

Answers (1)

Special Sauce
Special Sauce

Reputation: 5594

If we assume that id is an auto-increment field, then this is almost certainly due to the fact that timestamp column is not assigned at the same physical time the id column is assigned. For example, if you have a multi-page registration from a website application, the initial object may be inserted into the database to contain the initial data fields gathered on the first page. But perhaps the timestamp is only set after the final page of registration is complete. So depending on the time the user takes to complete registration, the timestamp can be out-of-sync with the id order.

There are multiple ways to work around this. One way is to assign timestamp when the record is first inserted into the database, or you could use a DB insert trigger to populate the timestamp field instead.

There is also the possibility that timestamp represents the date and time the user last modified their profile (or otherwise caused modification of the database record). In this case, the timestamp could be changing to a "future" value while the id stays constant.

Upvotes: 1

Related Questions