从具有不同时区的表中获取当前日期记录

Question

这个问题与我的预览一有关：如何在Laravel 中更改不同表中的时间zome

在我标记答案后，发现一个问题：

当用户加载页面时，它需要为他提供当天的记录。

例如从00:00:00到现在。

问题是，控制器要求DB从UTC的00:00:00开始提供记录，在此之后，我的模型(在上面的链接中(将其解析为本地时间。

当地时间是欧洲/索非亚(+3小时(，从今天03:00:00开始，将失去3项记录。

知道怎么修吗？

控制器：

function getdata_chart(Request $request)
{
$start_date = date('d-m-Y 00:00:00');
$end_date = date('d-m-Y 23:59:59');
if($request->start_date != '' && $request->end_date != '')
{
// if user fill dates
$dateScope = array($request->start_date ." 00:00:00", $request->end_date ." 23:59:59");
} else {
// default load page - today
$dateScope = array($start_date, $end_date);
};
$students = MeasCanal::whereBetween('recordtime', $dateScope)
->selectRaw('recordtime')
->selectRaw('max(formattedvalue) filter (where fullname = 'Данни.Кота') as iazovir')
->selectRaw('max(formattedvalue) filter (where fullname = 'Данни.Температура') as temperatura350')
->where(function ($query) {
$query->where('fullname', 'like', "Язовир.Данни.Кота")
->orWhere('fullname', 'like', "ГСК_11_350.Данни.Температура");
})
->groupBy('recordtime')
->orderBy('recordtime')
->get();
return response()->json($students);
}
return response()->json($students);
}

型号：

class MeasCanal extends Model
{
protected $connection = 'MeasCanal';
protected $table = 'meas_kanal';
protected $fillable = ['fullname','formattedvalue','recordtime','qualitydesc','statedesc','id'];

/**
* Get the user's recordtime.
*
* @param  string  $value
* @return string
*/
public function getRecordtimeAttribute($value)
{
return Carbon::parse($value)->timezone('Europe/Sofia')->toDateTimeString();
}
}

Answer 1

您必须通过调用carbon方法和数组映射函数将用户输入日期从DB时区映射到用户时区。并按原样调用查询。

function getdata_chart(Request $request) {
$start_date = date('d-m-Y 00:00:00');
$end_date = date('d-m-Y 23:59:59');
if($request->start_date != '' && $request->end_date != '')
{
// if user fill dates
$dateScope = array($request->start_date ." 00:00:00", $request->end_date ." 23:59:59");
} else {
// default load page - today
$dateScope = array($start_date, $end_date);
};

$dbTimeZone = 'Asia/Kolkata'; // update with your db timezone
$userTimeZone = 'Europe/Sofia';
$dateScope = array_map(function($date) use ($dbTimeZone, $userTimeZone) {
return Carbon::createFromFormat('d-m-Y H:i:s', $date, $dbTimeZone)->setTimezone($userTimeZone);
}, $dateScope);
$students = MeasCanal::whereBetween('recordtime', $dateScope)
->selectRaw('recordtime')
->selectRaw('max(formattedvalue) filter (where fullname = 'Данни.Кота') as iazovir')
->selectRaw('max(formattedvalue) filter (where fullname = 'Данни.Температура') as temperatura350')
->where(function ($query) {
$query->where('fullname', 'like', "Язовир.Данни.Кота")
->orWhere('fullname', 'like', "ГСК_11_350.Данни.Температура");
})
->groupBy('recordtime')
->orderBy('recordtime')
->get();
return response()->json($students);
}
return response()->json($students);
}

请注意：-如果您尚未使用碳，请在控制器中添加以下行。

使用Carbon\Carbon；

Answer 2

问题是导致Unexpected data found.的每个函数的日期分隔符不同。在编辑他之后，萨钦·库马尔的回答完成了任务。谢谢你。只要用类似的长期解决方案发现问题：

$start_date0 = date('d-m-Y 00:00:00');
$end_date0 = date('d-m-Y 23:59:59');
$start_date = Carbon::createFromFormat('d-m-Y H:i:s', $start_date0, 'Europe/Sofia')->setTimezone('UTC');
$end_date = Carbon::createFromFormat('d-m-Y H:i:s', $end_date0, 'Europe/Sofia')->setTimezone('UTC');

if($request->start_date != '' && $request->end_date != '')
{
// if user fill dates
$start_date0 = $request->start_date;
$end_date0 = $request->end_date;
$start_date = Carbon::createFromFormat('d/m/Y H:i:s', $start_date0, 'Europe/Sofia')->setTimezone('UTC');
$end_date = Carbon::createFromFormat('d/m/Y H:i:s', $end_date0, 'Europe/Sofia')->setTimezone('UTC');
$dateScope = array($start_date, $end_date);
} else {
// default load page - today
$dateScope = array($start_date, $end_date);
};