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
– Tim Biegeleisen

2016-04-12 14:59:14 +00:00
Commented Apr 12, 2016 at 14:59 — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 12, 2016 at 14:59
the type is datetime

ray
– ray

2016-04-12 15:00:12 +00:00
Commented Apr 12, 2016 at 15:00 — ray
– ray, Commented Apr 12, 2016 at 15:00

Sevle · Accepted Answer · 2016-04-12 15:16:40Z

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, 2016 at 15:16

Sevle

3,1192 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ray Over a year ago

Thanks is there a way to make it work if $date is a string I have tried using strtotime but that doesn't work

Sevle Over a year ago

check @Matthew's answer here

ray Over a year ago

I solved it like this passing in $newDate instead of date. $originalDate = $date; $newDate = date("d-m-Y", strtotime($originalDate));

ceejayoz · Accepted Answer · 2016-04-12 15:21:34Z

0

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();

answered Apr 12, 2016 at 15:21

ceejayoz

181k42 gold badges311 silver badges382 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related