CODEWITHSUNDEEP
phpmysqllaravel
Mikail G.
Mikail G.

Reputation: 478

Laravel mysql php Timestamps

I'm trying to count unread messages in laravel app, however my "last_viewed" column contains "raw" time in seconds stored in "last_viewed (int) 11" and my "created_at" contains mysql timestamp so I can't compare them in mysql statement. Here is how I compare them, but it's kind of spaghetti 

class Helper {

	public static function count_unread_messages()
	{
		        $result = Conversation::join('messages','messages.conversation_id','=','conversation.id')
		                ->join('conversation_members','conversation_members.conversation_id','=','conversation.id')
		                ->select('conversation_members.last_viewed as last_viewed',
		                	'messages.created_at as created_at')
		                ->where('conversation_members.user_id','=',Auth::user()->id)
		                ->groupBy('messages.id')->get();
		$i = 0;
		foreach ($result as $key) {
			    if (date($key->last_viewed) < date(strtotime($key->created_at))) {
			    	$i++;
			    }			
		}
		return $i;
	}
}

but I'd like to compare inside Mysql statement, like that ->where('last_viewed' ,'<', 'created_at')->count()

Is there a way to change format of a timestamp into seconds inside a mysql statement?

Upvotes: 0

Views: 673

Answers (1)

Gayan
Gayan

Reputation: 3704

I think the best way to solve this is to persist time stamps as carbon instances.

You can do that by setting protected $dates = ['last_viewed', 'created_at']; in your Conversation model.

Now when the data get persisted, since last_viewed and created_at persists as carbon instances and mainly because eloquent support carbon, you can simply achieve

->where('last_viewed', '<', 'created_at')->count() functionality.

Hope this was helpful.

Upvotes: 1

Related Questions