使用Laravel在PHP中按时间戳，后端过滤SQL数据库查询

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.

所以我正在尝试从我的后端构建一个查询到我的SQL数据库(用Laravel编写的php)。我的数据库中有一个表,其标题为“created_at”,带有'timestamp'类型的值。我想要做的是,当我向我的数据库发送查询时,只选择从当前日期30天或更短时间创建的表格的行(或我猜)。我尝试过使用'where'关键字来回实验,但我无法让它按照我的意愿运行。

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.

当我在我的数据库中浏览表格时,日期以“YYYY-MM-DD hh:mm:ss”的格式写出,但我不确定如何以这种格式比较它们,我不知道知道如何在进行查询时转换它们。我已经查看了Laravel中的whereBetween()方法,但由于我不知道如何比较它们,所以我没有这样做。

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!

提前致谢!

2 个解决方案

#1

It's basically that simple.

基本上这很简单。

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

2 个解决方案

#1

更多相关文章

随机推荐