CODEWITHSUNDEEP
phplaravellaravel-5
fabian
fabian

Reputation: 5463

Laravel Eloquent whereDate() with UnixTimestamps

the entries in my database have a custom timestamp field (e.g. 1488446089). How do I use whereDate() or whereDay() with unix timstamps?

This of course just works for Date Fields.

$posts = Post::whereDay('uploaded_time', '=', '2017-05-10')->get();

Upvotes: 1

Views: 3413

Answers (2)

JJWesterkamp
JJWesterkamp

Reputation: 7916

One way to do this is fetching all Posts, and then filtering the results in PHP:

    $myDate = '2017-05-10';

    $posts = Post::all()->filter(function (Post $post) use ($myDate) {
        $uploadedAt = Carbon::createFromTimestamp($post->uploaded_time)->format('Y-m-d');
        return $uploadedAt === $myDate;
    });

Why? Because if I'm right you are trying to fetch all posts that were uploaded on a certain date. This means that there is a range of timestamps valid. (60 * 60 * 24). With Carbon (comes with Laravel) you can easily convert your timestamps to Y-m-d formatted strings. Those you then compare to the date string of your choice.

Edit

I have no way to test this at the moment, but it should be possible to do this with a query builder after all:

$myDate = Carbon::createFromFormat('Y-m-d', '2017-05-10');
$startOfDayTimestamp = $myDate->startOfDay()->timestamp;
$endOfDayTimestamp = $myDate->endOfDay()->timestamp;

$posts = Post::whereBetween('uploaded_time', [
    $startOfDayTimestamp,
    $endOfDayTimestamp
])->get();

Here we create 2 timestamps, one for the very start of the date you wish to filter by, and one for the very end. After that, we can use the builder's whereBetween() method and pass the 2 timestamps.

I found this solution here, and simplified a little bit by replacing the setTime() calls with startOfDay and endOfDay methods.

Upvotes: 1

Niklesh Raut
Niklesh Raut

Reputation: 34914

To run it correctly use whereDate instead of whereDay 

$posts = Post::whereDate('uploaded_time', '2017-05-10')->get();

To get all specific day means (date of month like 1-30) 

$posts = Post::whereDay('uploaded_time', '10')->get();//provide date of month

get more detail : https://laravel.com/docs/master/queries#where-clauses

Upvotes: 2

Related Questions