php - Laravel 中的日期格式查询

Question

我从数据库获取数据的查询是，

select mailboxtoolno,DATE_FORMAT(maileventdate,'%d %b %Y') as 
date,DATE_FORMAT(maileventdate,'%H:%i:%s') as time,mailtype from 
domiciliation_mailbox where reg_id =".$regid." order by id DESC

如何将其更改为 laravel eloquent 模型，

我一直在尝试改变它，

 $timeline= mailbox::select('mailboxtoolno','DATE_FORMAT(maileventdate,"%d %b %Y") as date','DATE_FORMAT(maileventdate,"%H:%i:%s") as time','mailtype')
->where('reg_id', '=',$reg_id )
->paginate(10);

但出现错误，例如，

Unknown column 'DATE_FORMAT(maileventdate,"%d %b %Y")' in 'field list'

如何在 Laravel 中获取正确的日期格式值

Answer 1

Laravel 不支持复杂的选择表达式，所以你必须使用 Raw Expressions 。尝试这样:

    $timeline= mailbox::select('mailboxtoolno',DB::raw('DATE_FORMAT(maileventdate,"%d %b %Y") as date'),DB::raw('DATE_FORMAT(maileventdate,"%H:%i:%s") as time'),'mailtype')
->where('reg_id',$reg_id )
->orderBy('id','DESC')
->paginate(10);

为了在此查询中使用 ->orderBy()，您必须手动设置严格模式以通过验证忽略订单。在 database.php 配置数据库连接数组参数中执行此操作:

'strict'    => true,
        'modes'     => [
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_AUTO_CREATE_USER',
            'NO_ENGINE_SUBSTITUTION'
        ],

或者设置strict => false(我不会这样做)