douliao7930
2019-04-30 03:58 阅读 46
已采纳

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

I want to add extra 30mins to "daily_attendances.in_time" while counting "delay day". Any idea how to do that?

$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();
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    doufenzhu7012 doufenzhu7012 2019-04-30 04:40

    For this case we have to use MySQL function called DATEADD

    Example:

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

    Output:

    2017-01-15 02:42:19
    

    I have modified your query, Please try it out.

    $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();
    

    You can also refer to the link

    点赞 评论 复制链接分享

相关推荐