XF 2.0 how to get data of current day/week/month/year with the Finder?

[DL6]

Hi, I'm working on a new widget that shows the most liked posts of current day/week/month/year, this test works well

Spoiler

$postIds = $this->db()->fetchAllColumn("
                    SELECT post_id
                    FROM xf_post
                    WHERE (DATE_FORMAT(FROM_UNIXTIME(post_date),'%Y-%m-%d')) = CURDATE()
                    AND message_state = 'visible'
                    AND likes > 0                
                    ORDER BY likes DESC
                    LIMIT 20
                ");

$postFinder = $this->finder('XF:Post');
$postFinder
        ->with(['Thread.Forum.Node.Permissions|' . $visitor->permission_combination_id, 'User'])
        ->where('post_id', $postIds)
        ->setDefaultOrder('likes', 'DESC')
        ->limit(max($limit * 2, 10));

but this makes 2 queries for each option, when I try with something like this I get an error

Spoiler

$postFinder->where('(DATE_FORMAT(FROM_UNIXTIME(post_date),'%Y-%m-%d'))','CURDATE()');

"Parse error: syntax error, unexpected '%' in src\addons\EMD\TemplateEdits\XF\Widget\MostLikedPosts.php on line 54"
is there another way to do this?

 

[Lukas W.]

You have to take care of your quotation marks. You're trying to take a string modulo the constant Y with your current code.

 

[DL6]

You have to take care of your quotation marks. You're trying to take a string modulo the constant Y with your current code.

Thanks, i try diferent ways but a think is no posible do this with the Finder, always returns errors like this
Template Compilation Error
public:forum_list - Unknown column (DATE_FORMAT(FROM_UNIXTIME(post_date), "%Y-%m-%d")) on XFost in src\XF\Mvc\Entity\Finder.php:1398

 

[Mike]

You can do what you using things like whereSql or finder expressions. However, I'm not going to get into that because it's simply not necessary here.

All you need to do is figure out the start and end date in UNIX timestamps for the date in question. For today, it's easy:

$date = new \DateTime();
$date->setTime(0, 0, 0);
$start = $date->getTimestamp();
$date->setTime(23, 59, 59);
$end = $date->getTimestamp();

(Note: not tested.)

Now you just need to limit >= $start and <= $end.

 

[DL6]

Thanks @Mike, works perfectly
just modify it a bit to work with the time zone of the visitor

$visitor = \XF::visitor();
$date = new \DateTime("now", new \DateTimeZone($visitor['timezone']));
$date->setTime(0, 0, 0);
$start = $date->getTimestamp();
$date->setTime(23, 59, 59);
$end = $date->getTimestamp();