Search code examples
sqlsqlitetimegroup-bystrftime

strftime () function in SQLlite and applying it for specific definition of the day

I try to use strftime('%d',time) function to make Group by () by the day. But the problem that I would like to define each day not like the usual day (from 00:00 to 23:59) but from 5:45 to 5:45 next day.

Is it possible to do in SQLlite?

Solution

  • I believe that the following may do what you wish :-

    strftime('%d',time) - (strftime('%H:%M',time) < '05:45')

    That is if the time is before 05:45 then 1 (true) is subtracted from the day when determining the GROUP BY argument and thus becomes the previous day.