PHP按created_at排序时凌晨时段数据始终排在首位的问题

Hey there, let's break down what's causing this sorting weirdness and fix it for you!

The Root of the Problem #

You've got two key issues here:

1. String-based sorting fails for time
   Your created_at field is set to varchar, which means the database sorts it like plain text instead of actual time. For example, "April 15 2018 12:07 am" (your 00:07 entry) will come before "April 15 2018 2:07 am" because the string starts with "1" instead of "2"—dictionary order doesn't care about time logic.
2. Poor timezone handling via formatted strings
   Using date_default_timezone_set() then storing a formatted date string is a fragile approach. It locks you into a human-readable format that's hard to sort, calculate, or adjust for different time zones later.

Fixes to Get Your Sorting Working Right #

Option 1: Change the Field Type (Highly Recommended) #

This is the cleanest, most maintainable solution:

• Alter your created_at column to use TIMESTAMP or DATETIME instead of varchar:
  • TIMESTAMP automatically stores dates in UTC and converts them to your server's (or user's) timezone on query—perfect for cross-timezone apps.
  • DATETIME stores the literal date/time, so you'll handle timezone conversion manually, but sorting still works flawlessly.
• When inserting data, store raw timestamps or standard date strings instead of your formatted version:

  // For DATETIME: store a standard ISO-compatible string
  $created_at = date('Y-m-d H:i:s');
  // Or better yet, let the database handle it directly with NOW() in your SQL query
  

• Now sorting is trivial and accurate:

  SELECT * FROM your_table ORDER BY created_at DESC;
  

Option 2: Quick Fix (If You Can't Modify the Table) #

If you're stuck with the varchar field for now, convert the string to a date during your query to sort correctly:

SELECT * FROM your_table 
ORDER BY STR_TO_DATE(created_at, '%M %d %Y %h:%i %p') DESC;

The STR_TO_DATE function parses your custom date format into a proper datetime value the database can sort logically. Note: This will slow down queries on large datasets, so use it only as a temporary band-aid.

Fix Your Timezone Workflow #

Stop storing formatted strings to handle timezones. Instead:

• Store UTC time (using TIMESTAMP makes this automatic).
• When displaying dates to users, convert the stored UTC time to their local timezone:

  date_default_timezone_set('Europe/London'); // Set user's timezone
  $display_time = date('F j Y g:i a', strtotime($db_stored_timestamp));
  

内容的提问来源于stack exchange，提问作者alpomvra