CODEWITHSUNDEEP
mysqllaravel
Punabaka Abhinav
Punabaka Abhinav

Reputation: 512

Adding one hour to datetime and comparing with present time based on hours and minutes

I have written a query where I am adding one hour to a datetime which is present in database, and comparing that time to the present time based on minutes and hours.

Table structure of upgrades:

 id| postid |type |status| counter| datetime            | autoreposttime

 1 |    139 |   M | P    |  1     | 2017-04-26 10:49:23 | 60    
 2 |    140 |   M | P    |  1     | 2017-04-26 10:49:27 | 60    
 3 |    141 |   M | P    |  1     | 2017-04-26 10:49:31 | 60    
 4 |    142 |   M | P    |  1     | 2017-04-26 10:49:34 | 60

Table structure of posts:

post_id | locationid | priority_time 

81      | 1          | 2017-04-20 18:29:17  
82      | 27         | 2017-04-20 18:29:19  
85      | 27         | 2017-04-20 18:29:07

Here is my SQL query which I have written in where I want to retrieve rows where datetime + 1 hour is equal to present time.

$posts = DB::table('upgrades')
        ->join('posts','posts.post_id','=','upgrades.postid')
        ->where(DB::raw('DATE_FORMAT(DATE_ADD(`upgrades`.`datetime`, INTERVAL 1 HOUR),"%Y-%m-%d %H:%i")'),$datetime)
        ->select('posts.*','upgrades.*')->get();

I'm getting data as null, and I think there's some problem in the where condition. What is wrong with my query?

Upvotes: 1

Views: 143

Answers (1)

Alexey Mezenin
Alexey Mezenin

Reputation: 163798

One way to do that is to use Carbon. If I understood you correctly, you want result for specified minute, so here's an example:

$time = Carbon::now()->subHour();
$posts = ....
    ->whereBetween('datetime', [$time->format('Y-m-d H:i:00'), $time->format('Y-m-d H:i:59'])
    ....
    ->get();

Upvotes: 1

Related Questions