On 2016/05/14 8:28 PM, Daniel Polski wrote:
> Hello,
> BETWEEN doesn't give the result I would need when used with time
> before & after midnight:
>
> SELECT time('23:00') BETWEEN time('22:00') AND time ('23:30');
> time
> ----
> 1
>
> sqlite> SELECT time('23:00') BETWEEN time('22:00') AND time ('01:00');
> time
> ----
> 0
>
> Any suggestion how to tweak the query to give "true" as the result
> even with midnight wrap around?
You are asking the wrong thing to check in a wrong boundary. 23:00 is
definitely NOT in between 22:00 and 01:00, unless you mean 01:00 the
NEXT day - in which case, the moment you introduce DAY into the
equation, you need a full date and the time is no longer enough by itself.
SELECT ( datetime( '2016-05-12 23:00:00' ) BETWEEN datetime( '2016-05-12
22:00:00' ) AND datetime( '2016-05-13 01:00:00' ) ) AS time;
time
----
1
If you do not care about the dates, you can introduce a dummy date added
to make things sensible.
Simply adding the standard strings '2000-01-01 ' and '2000-01-02 ' in
front of any time (with the 02 where the time is past midnight) should
sort out a fudge that will make things work, with the added
inconvenience you have to keep track of when you talk about a time that
is "past midnight" - programmatically easy, but not so easy in queries.
Best of luck!
Ryan
