How to filter time in a datime field in eloquent?

Asked

Viewed 867 times

3

How can I compare if a time is equal to a time stored in a column datetime using the Eloquent?

php datetime laravel-eloquent date laravel-5.1

1 answer

5


Whereas you want to disregard the date but consider only the time, you need to use the function TIME Mysql to format column with format datetime for time format. Therefore, it is necessary to use the method DB::raw() to accept a Mysql expression in WHERE.

Example:

 // Pega todos os usuários que foram cadastrados às 23:59:01

 Usuario::where(DB::raw('TIME(created_at)'), '=', '23:59:01')->get();

If you want to ignore the seconds, you’ll probably have to make a whereBetween, and specify the seconds 00 and 59. Thus:

 // Retorna os usuários que foram cadastrados às 13:38, independente dos segundos 

 Usuario::whereBetween(DB::raw('TIME(created_at)'), ['13:38:00', '13:38:59'])->get();

If you need these schedules dynamically, you can use the function date with the parameter H:i:s.

  Usuario::where(DB::raw('TIME(created_at)'), '=', date('H:i:s'))->get()

Moreover, it is perfectly possible to set the current date, ignoring the seconds, for the case of whereBetween

Usuario::whereBetween(DB::raw('TIME(created_at)'), [date('H:i:00'), date('H:i:59')])->get();

Updating

The function HOUR that I had previously put was wrong. HOUR returns the whole time value of a date.

For example:

 2015-01-01 23:40:30

Retonaria:

 23

Already the function TIME returns 23:40:30, like String.

  • 1

    Who gave the negative could explain? Because I did the answer exactly as the AP asked.

  • Look, I didn’t give the negative, but your answer is wrong or the user asked wrong, because? hour takes only the hour and what I could perceive in his question is hours, minutes and seconds of a datetime? see the question is very confusing and even your answers are wrong because you take example 19 hours and compare with a full hour also have problems

  • That’s exactly what he told me to do in the comments. Take a look there.

  • Even though your comparisons are wrong, that’s what I’m trying to say!

  • How wrong? Did you test it? I tested it here and it worked. He asked to take "the time it was stored". If it was stored 13:10:01 of any date, the query works correctly. Now, if the user needs something else, it is he who needs to specify in the question (and this would not be valid reason for negative).

  • Caro Wallace Usuario::where(DB::raw('HOUR(created_at)'), '=', '23:59:01')->get(); you need to test why 23 != '23:59:01' or I’m wrong?

  • I don’t think I understand 23 there. The user asked to search by the time, and not by "time".

  • @Virgilionovic ahhhhhhhhhhhhhhhhhh, yes. You’re right. I put the wrong function. The right one is TIME

  • if the user asked to check by blz time, then it should be Usuario::where(DB::raw('HOUR(created_at)'), '=', '23') ??? or not. because Hour returns only the time value

  • 1

    @Virgilionovic thanks for pointing. Really... The HOUR returns the time, the TIME returns 23:59:01.

  • maybe now the person who saw your problem put the up for you! it was good he had said... neh but, that’s not how it works for some users

  • 1

    @Virgilionovic I’m not quite sure, kkk. If those who negatived earlier had pointed it out, I would have already done the editing. But thank you very much, man. I had tested with TIME and put HOUR in response, a big knife, kkkkk

Show 7 more comments

Browser other questions tagged php datetime laravel-eloquent date laravel-5.1

You are not signed in. Login or sign up in order to post.