Thread

I need to select entries which has been edited within the last 24 hours.

I found this query:

{exp:query sql="SELECT * from exp_weblog_titles WHERE entry_date > ((unix_timestamp(Now()))-(7*86400))"}
{entry_id} {title}

{/exp:query}

But realised that I can’t use it on edit_date because it is stored in another time format, not the unix-timestamp.

I think it must be possible to use the second count where 86400 = 24 hours, but my Sql my skills are not good enough to build a query.  😖

Kind regards
Ole

How about using orderby=“edit_date” in conjunction with stop_before=“2009-11-11 00:00” in your weblog tag, or something like that? Never tried it but might work.

Hi Ian
Thanks for your replay, but the start_before and stop_before parameters refers to the entry date and not the edit_date. So it will give a solution where entries created in, for example, the last 24 hours will be sorted
by edit_date.

Aha! That’s probably why I haven’t used it before. That case I’m out of ideas I’m afraid.

it’s actually easier than when you have to deal with UNIXTIME:

{exp:query sql="SELECT * FROM exp_weblog_titles WHERE edit_date > NOW()-86400 ORDER BY edit_date DESC"}
    {entry_id} {title}

{/exp:query}

NOW(), when used in a numeric context returns a number in YYYYMMDDHHMMSS.uuuuuu format.

Very nice, except I made a logical mistake. The edit_date format is YYYYMMDDHHMMSS, so I can’t count in seconds like with the UNIX time.

You can, of course, convert to a timestamp first:

SELECT * FROM `exp_weblog_titles` 
WHERE UNIX_TIMESTAMP(NOW())-86400 <= UNIX_TIMESTAMP(edit_date) 
ORDER BY edit_date DESC

Thanks, I must try to learn this Sql language properly 😊