Level 122
What column type is the reading?
Apr 11, 2019
8
Level 1
Extract date value of timestamp as a comparison
I am passing a collection of tag movements from Controller to View. The collection items each have a timestamp (ReadingTimestampUTC) and a scan direction () which will either be in or out. Each record represents someone swiping in or out of the office card swipe system.
In my view in a particular place I want to display the actual time when a person scanned out today. Currently I have the following in my View which doesn't work unless a swipe out is done exactly at midnight:
{{$tagmovements->where('ReadingTimestampUTC',\Carbon\Carbon::today())->where('ScanDirection','OUT')->pluck('ReadingTimestampUTC')}}
The problem is the 'ReadingTimestampUTC' is a datetime value and I just want it to be a date value with the time stripped out so it matches the \Carbon\Carbon::today() which is a date only. (time is 0).
Is there any way I can just get the date component of the timestamp in this code? I tried this:
{{$tagmovements->where(date("Y-m-d",'ReadingTimestampUTC'),\Carbon\Carbon::today())->where('ScanDirection','OUT')->pluck('ReadingTimestampUTC')}}
but I get an error of "date() expects parameter 2 to be integer" which isn't surprising. My alternative, if I can't get this working, it to add another field with just the date value to do the comparison.
Level 1
@SNAPEY - ReadingTimestampUTC column is datetime (SQL Server)
Level 122
ok, so you can use the query builder ->whereDate() method
from the docs:
The whereDate method may be used to compare a column's value against a date:
$users = DB::table('users')
->whereDate('created_at', '2016-12-31')
->get();
So, ideally, filter the database query before passing to the view
Level 1
@SNAPEY - Yeah, seems like I will need to do it in the view. Problem is that I still need the full datetime to put it in the view so I will have to see how to add a field in the Controller.
Level 122
So you don't want to filter the results to just the date range? You still want to pass all the results to the view and then filter there?
Level 1
@SNAPEY - That was always my preference but I am not sure if it is possible or not.
Basically the view is a timesheet or a calendar.
The view has 1 table cell for each day, hence the need for the date value only - to see which cell to put it in.
Then the contents of each cell will be the exact time out, hence the need to retain the exact time through to the view.
Level 122
well that would seem easy then. Is there only one timestamp per day, or do you need to find the last?
what do you want to do for days where there is no timestamp? Is this a grid of timestamps or days?
Level 1
@SNAPEY - Yes, only 1 movement per day at the moment. The grid is of timestamps - when there is no timestamp I will either put some sort of warning message or I will just leave it blank.
Please or to participate in this conversation.