admin end post published time display not working correctly

Question

I have the inconsistency in post published time. For example, as I published a post on April 20nd 9pm and it is April 20nd 9:30, wordpress shows it is published one minute ago in the admin end. Also in case, a post is published like 18 hours ago, wordpress says it is published only an hour ago. (it display correct time as it passed a day)

Accordingly(perhaps) my code below does not work correctly.

function filter_where( $where = '' ) {
    $where .= " AND post_date > '" . date('Y-m-d',strtotime('-1440 minutes')) . "'";
    return $where;
}
add_filter( 'posts_where', 'filter_where' );    

This code supposed to query posts within a day time frame, but it actually displaying posts within 37 hours time frame.

I have set a server time and wordpress time zone correctly and consistently but have no good result back.

Answer 1

Your computer's time and your server's time may not be exactly synchronized. So you may be seeing some pseudo-issues because of that.

I don't know where you are seeing "published 1 minute ago", or anything like that, in the backend. I see a "published on" date and a "last edited" date but those are 'hard' dates not dates relative to "right now" the way StackExchange displays question and answer dates.

That is not really how I'd check the date. The date format in the database is 0000-00-00 00:00:00. That is "YEAR MONTH DAY HOUR:MINUTE:SECOND". You are comparing that to a date like 0000-00-00. That might work, mostly, but there may be edge cases and accepting that possibility is unnecessary. Untested but I suspect that you are probably getting posts dated not 24 hours ago but "second 0 of yesterday". Since you are not passing a time component I suspect that if you check for "2013-04-20" minus one day you will get everything starting from "2013-04-20 00:00:00" and nut just for the last twenty four hours. I am pretty sure that is why you saw 37 hours. If you test I expect that time difference changes but you will never see more than 48 hours.

WordPress uses a datetime format for those date columns, and MySQL can do its own date calculations. You have a number of what I would consider more robust choices for queries.

1. Convert the MySQL format post_date to UNIXTIME.

   $where .= " AND UNIX_TIMESTAMP(post_date) > '" . strtotime('-1440 minutes') . "'";
   

2. Convert the UNIXTIME value returned by strtotime to a MySQL Format

   $where .= " AND post_date > FROM_UNIXTIME('.strtotime('-1440 minutes').')";
   

3. Let MySQL do the whole thing.

   $where .= " AND post_date > DATE_SUB(CURDATE(), INTERVAL 1 day) ";
   

   This option means that the MySQL server's date has to match your HTTP server's date to a tolerable precision. That could be an issue if the servers are on different machines and you don't have direct control of both.

4. You could of course just feed MySQL a completely formatted date.

   $where .= " AND post_date > '" . date('Y-m-d H:i:s',strtotime('-1440 minutes')) . "'";
   

   But you are manually doing what MySQL can do for you, which strikes me as somewhat less reliable.