0

For example I have in database allowed ranges - (08:00-12:00), (12:00-15:00) and requested range I want to test - (09:00-14:00). Is there any way to understand that my test range is included in allowed range in database. It can be splited in even more parts, I just want to know if my range fully fits to list of time ranges in database.

sl_bug
  • 5,066
  • 5
  • 21
  • 22
  • my test range can be (09:00-10:00), (10:00, 14:00) – sl_bug Sep 14 '17 at 20:57
  • 1
    Please **[EDIT]** your question and add some sample data and the expected output based on that data. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [**no screen shots**](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). **[edit]** your question - do **not** post code or additional information in comments. –  Sep 15 '17 at 10:01

1 Answers1

0

You don't provide table structure, so I have no idea of data type. lets assume those are texts:

t=# select '(8:00, 12:30)' a,'(12:00, 15:00)' b,'(09:00, 14:00)' c;
       a       |       b        |       c
---------------+----------------+----------------
 (8:00, 12:30) | (12:00, 15:00) | (09:00, 14:00)
(1 row)

then how you can do it:

t=# \x
Expanded display is on.
t=# with d(a,b,c) as (values('(8:00, 12:30)','(12:00, 15:00)','(09:00, 14:00)'))
, w as (select '2017-01-01 ' h)
, timerange as (
select
  tsrange(concat(w.h,split_part(substr(a,2),',',1))::timestamp,concat(w.h,split_part(a,',',2))::timestamp) ta
, tsrange(concat(w.h,split_part(substr(b,2),',',1))::timestamp,concat(w.h,split_part(b,',',2))::timestamp) tb
, tsrange(concat(w.h,split_part(substr(c,2),',',1))::timestamp,concat(w.h,split_part(c,',',2))::timestamp) tc
from w
join d on true
)
select *, ta + tb glued, tc <@ ta + tb fits from timerange;
-[ RECORD 1 ]----------------------------------------
ta    | ["2017-01-01 08:00:00","2017-01-01 12:30:00")
tb    | ["2017-01-01 12:00:00","2017-01-01 15:00:00")
tc    | ["2017-01-01 09:00:00","2017-01-01 14:00:00")
glued | ["2017-01-01 08:00:00","2017-01-01 15:00:00")
fits  | t

first you need to "cast" your time to timestamp, as there is no timerange in postgres, so we take same day for all times (w.h = 2017-01-01) and convert a,b,c to ta,tb,tc with default including brackets (which totally fits our case).

then use union https://www.postgresql.org/docs/current/static/functions-range.html#RANGE-FUNCTIONS-TABLE operator to get "glued" interval

lastly check if the range is contained by the larger one with <@ operator

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • "*as there is no timerange in postgres*" but you can very easily create one: `create type timerange as range (subtype = time);` –  Sep 15 '17 at 10:02
  • @a_horse_with_no_name thank you - I will update answer with neater version as you suggest after knowing the table structure and a clear sample from OP – Vao Tsun Sep 15 '17 at 10:23