从 Mysql 数据库时间戳 Laravel 中仅选择当周的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27209202/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select only records of the current week from Mysql Database Timestamp Laravel
提问by user3177305
I want to get only the records from monday to friday of the current week, but i'm having a hard time thinking and researching ways on how to do it, I found some ways on the internet but its so hard to understand for a newbie like me, so here is my code:
我只想获取本周周一到周五的记录，但我很难思考和研究如何去做，我在互联网上找到了一些方法，但对于新手来说很难理解像我一样，所以这是我的代码：
$myquery=DB::table('attendances')
->leftJoin('employees', 'attendances.user_id', '=', 'employees.id')
->where('user_id', '=', $employee->id)
//I want to do something like this one
->WHERE WEEK(`date`) = WEEK(NOW()
->orderBy('attendances.date','asc')->get();
I implement some of the answers like this
我实现了一些这样的答案
$myquery=DB::table('attendances')
->leftJoin('employees', 'attendances.user_id', '=', 'employees.id')
->where("WEEKOFYEAR(`date`)", "=", "WEEKOFYEAR(NOW())")
->where('user_id', '=', $employee->id)
->orderBy('attendances.logon','asc')->get();
回答by Joseph Silber
You can use a whereBetweenwith two Carbondate instances:
您可以将 awhereBetween与两个Carbon日期实例一起使用：
->whereBetween('date', [
Carbon\Carbon::parse('last monday')->startOfDay(),
Carbon\Carbon::parse('next friday')->endOfDay(),
])
If you want to find all record in the current month, use this:
如果要查找当月的所有记录，请使用以下命令：
->whereBetween('date', [
Carbon\Carbon::now()->startOfMonth(),
Carbon\Carbon::now()->endOfMonth(),
])
回答by Spencer Wieczorek
You will want to use WEEKOFYEAR()instead of just WEEK, which gets the current week index in a year. So two dates in the same week will return the same WEEKOFYEAR. In normal SQL it would look like:
您将想要使用WEEKOFYEAR()而不仅仅是WEEK，它获取一年中的当前周索引。所以同一周的两个日期将返回相同的WEEKOFYEAR. 在普通 SQL 中，它看起来像：
... WHERE WEEKOFYEAR(`date`) = WEEKOFYEAR(NOW())
And in laravel:
在 Laravel 中：
->where("WEEKOFYEAR(`date`)", "=", "WEEKOFYEAR(NOW())")
回答by Wing Lian
I'd try something like
我会尝试类似的东西
->where('WEEK(FROM_UNIXTIME(`date`))', '=', 'WEEK(NOW())')
->where('DAYOFWEEK(FROM_UNIXTIME(`date`))', '>=', 2)
->where('DAYOFWEEK(FROM_UNIXTIME(`date`))', '<=', 6)
->where('WEEK(FROM_UNIXTIME(`date`))', '=', 'WEEK(NOW())')
->where('DAYOFWEEK(FROM_UNIXTIME(`date`))', '>=', 2)
->where('DAYOFWEEK(FROM_UNIXTIME(`date`))', '<=', 6)
回答by Nivedita Velagaleti
To get the data for the current week excluding saturday and sunday:
要获取当前周（周六和周日除外）的数据：
$query->where(DB::Raw('week(date)'), '=', Carbon::now()->weekOfYear)
->where(function($query){
return $query->where(DB::Raw('day(date)'),'<>',Carbon::SUNDAY)
->orWhere(DB::Raw('day(date)'), '<>',Carbon::SATURDAY); });
