Nyfiken Gul Nyfiken Gul - 4 months ago 27
PHP Question

Filter SQL database query by timestamp, backend in PHP using Laravel

So I'm trying to build a query to my SQL database from my backend (that's written in php with Laravel). I have a table in my database with a column that's titled "created_at", carrying values of the 'timestamp'-type. What I want to do is, when I send a query to my database, to only pick the rows (or instances, I guess) of my table that are created 30 days or less from the current date. I've tried experimenting back and forth with the 'where' keyword, but I couldn't get it to work as I wanted it to.

When I browse the table in my database, the dates are written in the format "YYYY-MM-DD hh:mm:ss" but I'm not sure how to compare them when they're in this format, and I don't know how to convert them whilst doing a query. I've looked at the whereBetween() method in Laravel but I didn't get that to work since I didn't know how I would compare them.

At the moment my query looks like this:

$topUser = DB::table('statistics')
->join('users', 'statistics.user_id', '=', 'users.id', 'inner')
->select('fname', 'user_id', DB::raw('sum(price) as total_collection'))
->groupBy('user_id')
->orderBy('total_collection','desc')
->get();


What I want to do, in pseudocode, is something along the lines of;

->where 'created_at' <= 30 days ago


Thanks in advance!

Answer

It's basically that simple.

->whereDate('created_at', '<=', \Carbon\Carbon::now()->subDays(30));
Comments