日期格式查询在Laravel
问题描述:
我从数据库中获取数据的查询,日期格式查询在Laravel
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雄辩模型,
我一直在试图改变它像,
$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中获得日期格式的正确值
答
使用raw
语句。例如:
$user = User::select(DB::raw('count(*) as user_count, status'))->where('status', '=', 'active');
顺便说一句,实际上,Laravel具有带DateTime类型字段的增变器。所以你可以选择正常的格式并稍后格式化。例;
$user = User::find(2);
$date = $user->created_at->format('d M Y'); // you can display it with any format you want with this way.
更多信息,请阅读official documentation和this
答
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()
在这个查询,你将不得不手动设置严格的模式,通过comprobations到ommit秩序。不要在你的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
(我不会做)
您使用的日期格式中选择查询,因此错误 –