How can I add 'Added Today' or 'Added yesterday' to my newly added records from mysql?

Question

I looked at the PHP date manual, but could not find any documentation about it. I found it on a dutch website http://marktplaats.nl. If you look at this url You can see 'Vandaag' this means 'Today', How do they did that?

The code I have now:

<?= date ("d M 'y",strtotime($row['aangeboden-sinds'])); ?>

[This might help](http://stackoverflow.com/questions/3454258/php-date-yesterday-today) — Jamie Taylor, Jul 23 '13 at 13:38
There's also a nifty [timeago](http://timeago.yarp.com/) plugin for jQuery if you're interested in something stackoverflow-like. (This also moved the time calculation to the client keeping the server from doing date calculations throughout the page, as well as a nicer UI since it auto-updates). — Brad Christie, Jul 23 '13 at 13:41
See my edit. I posted my current code :) Hopefully some people can help me how to implement @David's answer. — Kees Sonnema, Jul 23 '13 at 14:08

score 0 · Answer 1 · answered Jul 23 '13 at 13:41

0

It's probably just a simple conditional in the UI logic. The database itself just stores the date that something happened, and then in the display logic they might have something like (pseudo-code):

if (currentDate - storedDate < 1)
    echo "Today";
else if (currentDate - storedDate < 2)
    echo "Yesterday";
else
    echo storedDate;

You can add some additional logic to account for a rollover at midnight (for a particular time zone at least, unless you also have the user's time zone information to personalize the math) instead of a straight 24-hour comparison, but in the end it's really still just a conditional in the display logic, not anything special about how the data is stored.

answered Jul 23 '13 at 13:41

David

208,112
36
198
279

See my edit, I've added the code I currently have, because I don't know how to use this. – Kees Sonnema Jul 23 '13 at 14:04
@KeesSonnema: The code in the question should be successfully displaying the stored date, which would be the last condition in my pseudo-code. For the former conditions, you'd need to compare it with the current date. You can get difference between two dates in PHP: http://php.net/manual/en/datetime.diff.php – David Jul 23 '13 at 14:26
doesn't work. it gives `Message: Use of undefined constant storedDate - assumed 'storedDate'` – Kees Sonnema Jul 23 '13 at 14:31
@KeesSonnema: Are you just copying and pasting my pseudo-code? It doesn't work that way. I've demonstrated the logic, but this isn't an actual PHP implementation. Do you understand the logic? – David Jul 23 '13 at 14:32
Yes, but that's why I added my code. How do I name `currentdate` from my mysql? – Kees Sonnema Jul 23 '13 at 14:41
@KeesSonnema: Do you mean how do you get the current date or time? You can do it in PHP (http://php.net/manual/en/function.time.php) or MySQL (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_curdate). – David Jul 23 '13 at 14:47

Mike Sherrill 'Cat Recall' · Accepted Answer · 2013-07-23T16:05:03.223

Odds are good that this is done in SQL, not in application code. Here's how to do that in PostgreSQL. (The query seems to work without modification in MySQL 5.6, which surprised me a little bit.)

create table test (
  test_id integer primary key,
  created_at timestamp not null default current_timestamp
  );

insert into test values 
(1, current_timestamp),
(2, current_timestamp - interval '1' day),
(3, current_timestamp - interval '10' day);

Use a CASE statement and simple date arithmetic to do pretty much anything you want.

select test_id,
       case current_date - cast(created_at as date)
           when 0 then 'Added today'
           when 1 then 'Added yesterday'
           else 'Added some time ago'
       end as when_added
from test
order by created_at desc, test_id asc

TEST_ID  WHEN_ADDED
--
1        Added today
2        Added yesterday
3        Added some time ago

When you order by created_at desc, your data is naturally returned in display order (not considering whatever other columns your application might need), and in php you just need to display that "when_added" column. You don't need to do any manipulation in php.

How can I add 'Added Today' or 'Added yesterday' to my newly added records from mysql?

2 Answers2