Laravel Query using yyyy-mm--dd value on datetime column

Question

I have written the following query using the laravel query builder. I am trying to get the count of fields in a column for a userID, activityID, and a date. It is the date field input that is causing the issue. I want to be able to return all the fields that have the same date as the date value I pass in. Below is my code.

$points = DB:: table("users_has_activities")
-> where("activitiesID", "=", $this->activityID)
-> where("usersID", "=", $userID)
-> where("time", "=", $date)
-> count();

$date will be a value like "2016-04-10", and the field in my table will have values like '2016-04-10 21:01:27'. I need to be able to get all the fields where the day of the date value matches the day of the datetime value in the table.

What is the MySQL type of the `time` column? – Tim Biegeleisen Apr 12 '16 at 14:59 — Tim Biegeleisen, Apr 12 '16 at 14:59

score 1 · Accepted Answer · answered Apr 12 '16 at 15:16

1

You can use the whereDay() method to get the DATE from DATETIME type:

$points = DB:: table("users_has_activities")
-> where("activitiesID", "=", $this->activityID)
-> where("usersID", "=", $userID)
-> whereDay("time", "=", $date)
-> count();

answered Apr 12 '16 at 15:16

Sevle

3,109
2
19
31

Thanks is there a way to make it work if $date is a string I have tried using strtotime but that doesn't work – ray Apr 12 '16 at 15:23
check @Matthew's answer [here](http://stackoverflow.com/questions/6238992/converting-string-to-date-and-datetime/6239199#6239199) – Sevle Apr 12 '16 at 15:31
I solved it like this passing in $newDate instead of date. $originalDate = $date; $newDate = date("d-m-Y", strtotime($originalDate)); – ray Apr 12 '16 at 15:36

score 0 · Answer 2 · answered Apr 12 '16 at 15:21

The best way to handle this is a whereBetween as it'll be able to use database indexes and doesn't need a calculation for each row in the DB:

$points = DB:: table("users_has_activities")
-> where("activitiesID", "=", $this->activityID)
-> where("usersID", "=", $userID)
-> whereBetween("time", '2016-04-12 00:00:00', '2016-04-12 23:59:59')
-> count();

Laravel's bundled Carbon can be handy for generating these dates. If your column is flagged as a date (see https://laravel.com/docs/5.2/eloquent-mutators#date-mutators) they'll be carbon objects already:

$start = $date->copy()->startOfDay();
$end = $date->copy()->endOfDay();

Laravel Query using yyyy-mm--dd value on datetime column

2 Answers2