How do I select a date from a timestamp?

Let Eloquent be your friend - if you use a Model to represent the database tables then Laravel will automatically return date-types as Carbon instances. Where you will have all sorts of nice functionality.

Even if you're selecting raw queries from the database, without a Model of some sort, I'd recommend casting to a \Carbon\Carbon instance anyway, and then you have all the nice functionality for working with DATE objects, and you don't have to worry about the backing type of database at all.

(new Carbon\Carbon(DB::select('select * from users limit 1')[0]->created_at ))->toDateString()
or,
(new Carbon\Carbon(DB::table('users')->first()->created_at ))->toDateString()

Hi burlresearch, thank you for your reply and sorry for my late answer, I am getting back now to this project and faced the same problem again. Maybe I have a biased approach. I'll explain what I am trying to do. I am actually aggregating single records with database queries, like this:

$entries = static::
    ->selectRaw("DATE(visited_at) as date, HOUR(visited_at) as start_hour, patron_category_id, count(*) as visits")
    ->with('patronCategory')
    ->groupBy('patron_category_id', 'start_hour', 'date')
    ->get();

This way, I get a bunch of rows grouped by date and hour of the day, since in the end I am looking for the count of visits within each time slot.

I am trying to migrate this logic to the Collection level, after the records have already been returned from the DB, but I am stuck in a lot of nested closures...

What I am aiming for as a result of this query is the following data structure:

[
    [
        'date' => '2018-01-01',
        'hour' => 10,
        'category' => 'foo',
        'visits' => 12
    ],

    [
        'date' => '2018-01-01',
        'hour' => 11,
        'category' => 'bar',
        'visits' => 4
    ],
]

Can you post the first 2 or 3 $entries so we can see what you're starting with?

Of course:

=> Illuminate\Database\Eloquent\Collection {#825
     all: [
       App\LogbookEntry {#829
         date: "2018-01-17",
         start_hour: 8,
         patron_category_id: 1,
         visits: 4,
         patronCategory: App\PatronCategory {#818
           id: 1,
           name: "Students",
           abbreviation: "Stud.",
           is_active: 1,
           is_primary: 1,
           notes: null,
           created_at: "2018-01-17 10:08:45",
           updated_at: "2018-01-17 10:08:45",
         },
       },
       App\LogbookEntry {#830
         date: "2017-01-18",
         start_hour: 10,
         patron_category_id: 1,
         visits: 12,
         patronCategory: App\PatronCategory {#818},
       },
       App\LogbookEntry {#831
         date: "2018-01-17",
         start_hour: 10,
         patron_category_id: 1,
         visits: 2,
         patronCategory: App\PatronCategory {#818},
       },
       App\LogbookEntry {#832
         date: "2018-01-18",
         start_hour: 10,
         patron_category_id: 1,
         visits: 4,
         patronCategory: App\PatronCategory {#818},

This is what LogbookEntry::get() returns - without transformations:

>>> LogbookEntry::get()
=> Illuminate\Database\Eloquent\Collection {#861
     all: [
       App\LogbookEntry {#862
         id: 1,
         patron_category_id: 1,
         visited_at: "2018-01-17 10:08:55",
         recorded_live: 1,
       },
       App\LogbookEntry {#863
         id: 2,
         patron_category_id: 1,
         visited_at: "2018-01-17 10:08:55",
         recorded_live: 1,
       },
       App\LogbookEntry {#864
         id: 3,
         patron_category_id: 2,
         visited_at: "2018-01-17 10:08:57",
         recorded_live: 1,
       },
       App\LogbookEntry {#865
         id: 4,
         patron_category_id: 2,
         visited_at: "2018-01-17 10:08:57",
         recorded_live: 1,
       },
       App\LogbookEntry {#866
         id: 5,
         patron_category_id: 1,
         visited_at: "2018-01-17 08:00:00",
         recorded_live: 0,
       },

Sorry if I'm confused - I think I've lost track of your question. But this doesn't do what you want?

$entries = static::
    ->selectRaw(" DATE(visited_at) date, strftime('%H', visited_at) hour, patron_category_id category, count(*) visits ")
    ->groupBy('category', 'hour', 'date')
    ->get();

You may be able to use strftime to get the date-formatting you want. If that's not supported for SQLServer maybe this will help?

Have you looked at using a date mutator? https://laravel.com/docs/5.5/eloquent-mutators#date-mutators

Hi guys, thank you very much for your contribution. Sorry, I have put too much on the fire with $entries and so on.

To summarize the main problem, here's what happened: originally, I used MYSQL datetime functions massively in order to pre-select aggregates at the database level before processing the resulting entries later with collection methods. However, one day I was told that I would have to deploy the project on Windows Server with Microsoft SQL Server - 70% of the tests obviously failed, since I had used a ton of MYSQL-specific functions.

The more I think about it, the more I am convinced that pre-aggregating the entries at the database level isn't worth the loss of compatibility between DBMS and sounds like premature optimization. As you suggested, burlresearch, I am trying to become Eloquent and Collection's friend, and migrate the logic to that level. ;)

Here is something I came up with today, still has a bit too many foreachs, but at least doesn't add another database query. In this example, the method exports the entries as an array for a CSV file creator. Instead of having two different fields for date and hour, I kinda concatenated them together in the field 'start_time'.

public function scopeExport($builder)
{
    $entries = $builder->oldest('visited_at')->get()
        ->groupBy(function ($item) {
            return $item->visited_at->format('Y-m-d H:00:00');
        })->map(function ($collection) {
            return $collection->groupBy('patronCategory.name');
        });
    
    /**
     * At this point, we've got a structure like:
     * [
     *      '2017-02-12 10:00:00 => [
     *          'FooCategory' => [
     *              Record1,
     *              Record2,
     *              Record3
     *          ],
     *          'BarCategory' => [
     *              Record1,
     *              Record2
     *          ],
     *      ]
     * ]
     */

    $visits = [];
    foreach ($entries as $datetime => $categories) {
        foreach ($categories as $category => $records) {
            $visits[] = [
                'start_time' => $datetime,
                'category' => $category,
                'visits' => count($records)
            ];
        }
    }

    return $visits;
}

As a side note, I am still convinced that something like selectDate('created_at') or selectHour('created_at') would be a nice abstraction in the query builder.