Laravel: Eloquent Querybuilder whereTimestamp?

user_5425161 0
Viewed 707

I have a query I am trying to build with the laravel eloquent query builder. I'm relatively new to Laravel and I'm looking for the method that will query against a date AND a time.

My use case: Fetch the child records for MyModel created in the last hour.

My query:

$childQuery = function($query) {
$timezone = new Timezone('UTC');
// whereDate only compares a Date value
// whereTime only compares the Time value
$query->whereDate('custom_timestamp' 
            ,'>='
            ,   Carbon::now($timezone)->addSeconds(-3600));
};
MyModel::with(['myChildModels' => $childQuery])->find($myModelId);

How do I perform a timestamp comparison? I'm sure I am just missing something simple, but amazingly enough I couldn't find a single reference in the docs to comparing timestamps.

eloquent laravel timestamp php
2 Answers

What's the data type of the custom_timestamp field? Are you talking UNIX timestamp (integer) or e.g. MySQL timestamp (string '2019-06-04 23:40:07')?

If custom_timestamp is an integer data type, then your query would be 

$childQuery = function($query) {
    $query->where('custom_timestamp','>=', time()-3600); // time() is always UTC
};
MyModel::with(['myChildModels' => $childQuery])->find($myModelId);

If custom_timestamp is a datetime data type, say a MySQL timestamp, your query would be something like

$childQuery = function($query) {
    $query->where('custom_timestamp','>=', now('UTC')->subHour());
};
MyModel::with(['myChildModels' => $childQuery])->find($myModelId);

This is MySQL syntax – your DB syntax may vary. The main thing is that you don't need to use Eloquent's date helper methods to work with dates. It's generally easier not to.

Also notice the global now() helper, and that you can pass 'UTC' directly as a string. One of the benefits of Laravel leveraging Carbon.

user_4672510

I can't test this right now, but I would have thought you just want your custom timestamp to be greater than one hour ago.

->whereDate('custom_timestamp', '>=', Carbon::now()->subHour())
user_42721620
Related