CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
Jonathan Morningstar
Jonathan Morningstar

Reputation: 431

Between Time in SQL

I am trying to write two SQL statements. The first would show between the hours of 11pm to 6am and the second would show NOT between the house of 11pm and 6am.

What I am trying to get my head around is when I say NOT between

WHERE TO_CHAR(MOPACTIVITY.MOPSTART, 'hh24:mi') NOT BETWEEN '23:00' AND '06:00'

I get the records BETWEEN '23:00' AND '06:00'. That seems opposite of what I am asking for in the SQL. What's up with that?

Upvotes: 2

Views: 1142

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

The code that you want is:

WHERE TO_CHAR(MOPACTIVITY.MOPSTART, 'hh24:mi') BETWEEN 'o6:00' AND '23:00'

and:

WHERE TO_CHAR(MOPACTIVITY.MOPSTART, 'hh24:mi') not BETWEEN '06:00' AND '23:00'

The between and not between operators always assume that the first bound is less than the second bound. So nothing ever matches:

where x between 2 and 1

And everything matches:

where x not between 2 and 1

Another way to write this is with hour:

WHERE hour(MOPACTIVITY.MOPSTART) between 6 and 23 - 1

The second is:

where hour(MOPACTIVITY.MOPSTART) NOT BETWEEN 6 and 23 - 1

There is a difference between using hour() and your formulation. Times such as 23:00:10 would match your time for 6-23, but would not match this version.

Upvotes: 0

jatal
jatal

Reputation: 840

I am not certain that your use of "BETWEEN" is what you intend. My concern is that "between" is "inclusive" of both specified values. Typically when filtering dates, you want an open-ended interval which does not include at least one of the boundaries.

In your question, for example, between will retrieve/omit the 06:00 and 23:00 minute. This is shown below to illustrate my point.

Using the section below "Setup for Test Data", I construct an edge-case test data set.

Here is the output of two queries:

Query for BETWEEN 06 and 23

select 
  m.mopstart
from 
  mopactivity m
where 1=1
  and to_char(m.mopstart, 'HH24:MI') between '06:00' and '23:00'
order by 
  m.mopstart
;

10-SEP-02 06.00.00.000000000 AM
10-SEP-02 06.01.00.000000000 AM
10-SEP-02 10.59.00.000000000 PM
10-SEP-02 11.00.00.000000000 PM    <-- Did you really want 23:00 record here?

Query for NOT BETWEEN 06 and 23

select 
  m.mopstart
from 
  mopactivity m
where 1=1
  and to_char(m.mopstart, 'HH24:MI') NOT between '06:00' and '23:00'
order by 
  m.mopstart
;

10-SEP-02 12.00.00.000000000 AM
10-SEP-02 05.59.00.000000000 AM
10-SEP-02 11.01.00.000000000 PM

Setup for Test Data ::

drop table mopactivity
;

create table mopactivity
(
  mopstart timestamp  NOT NULL
)
;

insert into mopactivity
values( 
  TO_TIMESTAMP ('10-Sep-02 22:59', 'DD-Mon-RR HH24:MI') 
)
;
insert into mopactivity
values( 
  TO_TIMESTAMP ('10-Sep-02 23:00', 'DD-Mon-RR HH24:MI') 
)
;
insert into mopactivity
values( 
  TO_TIMESTAMP ('10-Sep-02 23:01', 'DD-Mon-RR HH24:MI') 
)
;
insert into mopactivity
values( 
  TO_TIMESTAMP ('10-Sep-02 05:59', 'DD-Mon-RR HH24:MI') 
)
;
insert into mopactivity
values( 
  TO_TIMESTAMP ('10-Sep-02 06:00', 'DD-Mon-RR HH24:MI') 
)
;
insert into mopactivity
values( 
  TO_TIMESTAMP ('10-Sep-02 06:01', 'DD-Mon-RR HH24:MI') 
)
;
insert into mopactivity
values( 
  TO_TIMESTAMP ('10-Sep-02 00:00', 'DD-Mon-RR HH24:MI') 
)
;

commit
;

Upvotes: 0

APC
APC

Reputation: 146349

One solution is to use the 'SSSSS' format mask. It shows the number of seconds after midnight and is great for handling times irrespective of the date element.

Between the hours of 06:00 and 23:00 is

select * from t23
where to_number(to_char(some_date_coll, 'SSSSS')) is between (6*60*24) and (23*60*24);

Between the hours of 23:00 and 06:00 is

select * from t23
where to_number(to_char(some_date_coll, 'SSSSS')) < (6*60*24) 
     or to_number(to_char(some_date_coll, 'SSSSS')) > (23*60*24);

Or use NOT BETWEEN with the first query.

I've left the arithmetic in its expanded form because it's easier to understand (and amend) than hard-coded numbers of seconds.

The usual caveats about the performance impact of wrapping functions around indexed columns apply here.

Upvotes: 3

Related Questions