php - 如何在sql查询时向时间数据类型添加额外的分钟数

Question

我想在计算“延迟日”时向“daily_attendances.in_time”添加额外的 30 分钟。知道如何做到这一点吗？

$attendance_list = DB::table('daily_attendances')
        ->join('employees', 'employees.card_id', '=', 'daily_attendances.card_id')
        ->select('daily_attendances.*','employees.basic_salary','employees.start_time','employees.over_time_rate',
            DB::raw('SEC_TO_TIME( SUM( TIME_TO_SEC( daily_attendances.delay_time ) ) ) AS delay_time'),
            DB::raw('SEC_TO_TIME( SUM( TIME_TO_SEC( daily_attendances.extra_time ) ) ) AS extra_time'),
            DB::raw('SEC_TO_TIME( SUM( TIME_TO_SEC( daily_attendances.in_use_time ) ) ) AS duty_time'),
            DB::raw('count(daily_attendances.start_time < daily_attendances.in_time) AS delay_day'),
            DB::raw(DB::raw('count(*) as present_day')))
        ->whereRaw($where)
        ->groupBy('daily_attendances.card_id')->get();

Answer 1

对于这种情况，我们必须使用名为 DATEADD 的 MySQL 函数

示例:

SELECT DATE_ADD("2017-01-15 02:12:19", INTERVAL 30 MINUTE);

输出:

2017-01-15 02:42:19

我已经修改了您的查询，请尝试一下。

$attendance_list = DB::table('daily_attendances')
  ->join('employees', 'employees.card_id', '=', 'daily_attendances.card_id')
  ->select('daily_attendances.*','employees.basic_salary','employees.start_time','employees.over_time_rate',
      DB::raw('SEC_TO_TIME( SUM( TIME_TO_SEC( daily_attendances.delay_time ) ) ) AS delay_time'),
      DB::raw('SEC_TO_TIME( SUM( TIME_TO_SEC( daily_attendances.extra_time ) ) ) AS extra_time'),
      DB::raw('SEC_TO_TIME( SUM( TIME_TO_SEC( daily_attendances.in_use_time ) ) ) AS duty_time'),
      DB::raw('count(DATE_ADD(daily_attendances.start_time, INTERVAL 30 MINUTE) < daily_attendances.in_time) AS delay_day'), //change over here
      DB::raw(DB::raw('count(*) as present_day')))
        ->whereRaw($where)
        ->groupBy('daily_attendances.card_id')->get();

您还可以引用link