Laravel mysql php Timestamps

Question

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?

The function you're looking for is called FROM_UNIXTIME(), see documentation here: dev.mysql.com/doc/refman/5.5/en/…. You're going to have to use DB::raw() statements, by the way. Good luck! :) — Joel Hinz
– Joel Hinz, Commented Jan 26, 2015 at 21:34
thank You, but FROM_UNIXTIME() converts seconds into timestamp, is there a function which converts timestamps into seconds? — Mikail G.
– Mikail G., Commented Jan 26, 2015 at 21:45
The reverse is called UNIX_TIMESTAMP() - documentation here: dev.mysql.com/doc/refman/5.5/en/…. Although you can compare either seconds or dates directly, it'll result in the same thing. That said, do you really store in timestamp - not in datetime? — Joel Hinz
– Joel Hinz, Commented Jan 26, 2015 at 21:49
Yes I store 'created_at' in timestamp(it requiered by laravel 4) and 'last_viewed' in int11 seconds, however when I compare dates it gives me some random results, so I prefer comparing in seconds — Mikail G.
– Mikail G., Commented Jan 26, 2015 at 22:14

Gayan · Accepted Answer · 2016-12-08 16:07:05Z

1

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.

answered Dec 8, 2016 at 16:07

Gayan

3,7341 gold badge29 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel mysql php Timestamps

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related