CODEWITHSUNDEEP
phpmysqltimestamp
user3260621
user3260621

Reputation: 3

Order by timestamp broken?

I'm making a "Recent Donations" tab on my website.

I've made the query like this:

$result = mysqli_query($con, "SELECT * FROM dc_donations ORDER BY DATE(dt) LIMIT 5");

But when i got a new donation today. It seems like it puts the new one, at the end (So the new one, is ordered at the end). My theory is, that it orders normally, and 2014-01-30, comes before 2014-02-01.

The "dt" column of the table, is a TIMESTAMP field, autofilled with CURRENT_TIMESTAMP.

Upvotes: 0

Views: 72

Answers (2)

O. Jones
O. Jones

Reputation: 108806

I see two possible problems with your query.

 SELECT * 
   FROM dc_donations 
  ORDER BY DATE(dt)   /* slow and wrong ! */
  LIMIT 5

First is that you're storing a full-fledged date and time, yet when you do your ordering you're only ordering by the date portion. That's what DATE(dt) does. This causes you two issues. a) hopefully you'll have lots of donations, including newly arriving ones, in each calendar day. The way SQL ordering works, the server is allowed to order items unpredictably (randomly) if you don't completely specify the order. So your query will show a random collection of five old donations. b) using a function on a column in an ORDER BY clause defeats the use of an index for that operation. This could slow down your query a LOT as you scale up.

Second, do you want the five most recent donations? If so, use DESC. You want this query I believe.

 SELECT * 
   FROM dc_donations 
  ORDER BY dt DESC
  LIMIT 5

Pro tip: Don't use SELECT * in production software. Instead, enumerate the columns you need to retrieve.

Upvotes: 1

kero
kero

Reputation: 10638

The problem is that you don't define how exactly you want it to be ordered (only by which column). So MySQL wrongly assumes you want an ASCending order (smallest/oldest first).

This can be fixed by simply giving the correct order:

SELECT * FROM dc_donations ORDER BY DATE(dt) DESC LIMIT 5

But since dt already is a timestamp, you can save some time by ordering by the field itself instead:

SELECT * FROM dc_donations ORDER BY dt DESC LIMIT 5

Upvotes: 0

Related Questions