I have a mysql column with the following attributes
listDate TIMESTAMP DEFAULT = CURRENT_TIME
and I am displaying it in my table like this
echo "<td>" . $row['listDate'] . "</td>";
How would one go about converting the output to time since posted instead of the default:
2014-12-17 00:32:50
In your MySQL Query, you could use TimeStampDiff
SELECT
timestampdiff(MINUTE,listDate, NOW()) as minutes_from_list_date
FROM table_name
If you read the documentation, you should see a few ways on how you could change the output to what you might want/need.
to use it in your php code, you would call it like so:
echo '<td>'. $row['minutes_from_list_date'] . '</td>';
Use DateTime class
$timePosted = new DateTime ($row['listdate']);
$timeNow = new DateTime("now");
$timeSince = $timePosted->diff($timeNow);
echo $timeSince->format("Since posted passed %a days, %h hours, %i minutes and %s seconds");
Mysql has a function timestampdiff() and you can use it to generate the query something like below and its widely used in comment/reply section where it shows as
1 year ago, 10 days ago, 2 hours ago, just now etc
select
case
when timestampdiff(year,'2014-12-17 10:10:24',now()) > 0 then concat(timestampdiff(year,'2014-12-17 10:10:24',now()) ,' years ago')
when timestampdiff(month,'2014-12-17 10:10:24',now()) > 0 then concat(timestampdiff(month,'2014-12-17 10:10:24',now()) ,' months ago')
when timestampdiff(day,'2014-12-17 10:10:24',now()) > 0 then concat(timestampdiff(day,'2014-12-17 10:10:24',now()) ,' days ago')
when timestampdiff(hour,'2014-12-17 10:10:24',now()) > 0 then concat(timestampdiff(hour,'2014-12-17 10:10:24',now()) ,' hours ago')
when timestampdiff(minute,'2014-12-17 10:10:24',now()) > 0 then concat(timestampdiff(minute,'2014-12-17 10:10:24',now()),' minutes ago')
else 'just now'
end as posted ;
+-------------+
| posted |
+-------------+
| 3 hours ago |
+-------------+
In the query you can change the hard-coded datetime value with your column name.
The logic is
first it will check if the difference in terms of year is more than 0 then set the string posted {diff in years} ago
else if it is months then set
else if it is days then set
else if it is hours then set
else if it is in minute then set
else set the string as just now
