How to query a 'date_time' field for a 'date'

I am trying to get a list of records that are for a certain date ($most_recent_date = '2022-06-22')

Here is an example of the field 'date':

2022-06-21 00:00:00
2022-06-21 01:01:01
2022-06-21 02:02:02
2022-06-20 03:03:03
2022-06-20 00:00:00
2022-06-22 00:01:01
2022-06-22 00:02:02

I want 2 records:

2022-06-22 00:01:01
2022-06-22 00:02:02

How would I write this query?

I tried the following but it does not work (Undefined column: "DATE(date)" does not exist):

$summery = records::selectRaw(
			'license, 
            DATE(date) AS date_notime')        
        ->where('DATE(date)', '=', $most_recent_date) 
        ->groupBy('license', 'date')        
        ->get();

tykus

3 years ago

Best Answer

Level 104

If you want a raw expression in the constraint, use whereRaw; or, in your case you can use whereDate:

$summery = records::selectRaw('license, DATE(date) AS date_notime')        
        ->whereDate('date', $most_recent_date) 
        ->groupBy('license', 'date')        
        ->get();

1 like

scottsuhy

3 years ago

Level 1

That's good learning for me, thank you! Like all things, this has led me to another issue.

Invalid datetime format: 7 ERROR: invalid input syntax for type date: "{"date_notime":"2022-06-21"}"

I'm using Postgres and the field I dropped 'date' into is a 'timestamp without timezone' field. Do you think I chose the wrong type for the field?

scottsuhy

3 years ago

Level 1

The strange thing is if I change the query it works:

//->whereDate('date', $most_recent_date) 
        ->whereDate('created_at', '2022-07-24')

Here are the 2 fields side by side: https://imgur.com/6dTuUH8

and this works too:

->whereDate('date', '2022-06-21')

scottsuhy

3 years ago

Level 1

Dang, sorry. it was an easy fix. You rock!

->whereDate('date', $most_recent_date->date_notime)

Please or to participate in this conversation.