0

I have a application (postgresql 9.6 being migrated on 10) where I'd like to retrieve results within a table AND in the same time match opening hours stored within this table.

Let's explain with a fictive example: I have a table of stores:

store_name       |  opening_hours
-----------------+-----------------------------
storeA           | ((wday between 1 and 5) and (hour between 10 and 20))
storeB           | ((wday between 2 and 5) and (hour between 9 and 18)) OR (wday in (6,7) and (hour between 9 and 12))

I'd like to query this table and grab the opened store from the time of the query (no timezone bothering). (for whose who care: in my country 1st day of week is monday, but we dont care in this example) :

  • If my query ask for opened stores on a wednesday 19hour (7PM), It will return only storeA.
  • If the query is launched at midnight, none will be selected
  • if the query happend on thursday 11h (11AM) both store will be selected....

Can you help me make this little thing work ? I think i'm just missing the correct way to write it.

EDIT: the "opening hour" is only a thing to document the way I want to solve this problem. In no way, I'll add some new tables in this database. The only answer searched here is a way to evaluate expressions stored within tables.

urbain
  • 1
  • 2
  • Is that how your data is actually stored? A string with the data "((wday between 1 and 5) and (hour between 10 and 20))" or are you trying to give an example? – Barry Piccinni Jul 23 '18 at 14:16
  • Yes, it's exactly the way it's written within the table. But i'm opened to any simpliest way to do such a thing.. – urbain Jul 23 '18 at 14:19
  • Possible duplicate of [Best way to store working hours and query it efficiently](https://stackoverflow.com/questions/4464898/best-way-to-store-working-hours-and-query-it-efficiently) – Barry Piccinni Jul 23 '18 at 14:26

2 Answers2

0

Since you are open to suggestions, I suggest looking at the accepted answer to this question: Best way to store working hours and query it efficiently

Your current table structure would be quite hard to manage. If you altered the structure of the table to match the accepted answer in the above, it is exactly what you need, and would make it very simple to query.

EDIT: For completeness, the suggested table structure in the link is:

To store normal operation hours, you would need to store a number of records containing:

  • Shop - INTEGER
  • DayOfWeek - INTEGER (0-6)
  • OpenTime - TIME
  • CloseTime - TIME
Barry Piccinni
  • 1,685
  • 12
  • 23
  • I'll keep it for later if no found a smarter way to do this, but i'm quite sure postgresql has a clever mechanism to do such a thing in an elegant way. – urbain Jul 23 '18 at 14:39
  • @urbain Fair enough, you may well discover a way I've not considered, but I don't think you'll get an answer that's more simple or usable than that. – Barry Piccinni Jul 23 '18 at 14:52
0

Not fully satisfied by my answer below, but it works the way I want, not the mysql low-tech way. My work below is based on How to execute a string result of a stored procedure in postgres .

If it can helps, here it is:

-- push message to debug, to 'RAISE' usefull things
SET client_min_messages TO DEBUG;
\set VERBOSITY terse

-- must return a SETOF to evaluate my test (see RETURN QUERY EXECUTE below)
-- so here is a dirty simple [temporary] table.
CREATE TEMP TABLE stupid_bool_table (opened BOOLEAN);
INSERT INTO stupid_bool_table VALUES (true),(false);

CREATE OR REPLACE FUNCTION grab_worker_test_opening_hour(shopNametext)
  RETURNS SETOF stupid_bool_table AS
$BODY$
  DECLARE
  -- $Id: batch_workers.psql,v 1.15 2018/07/25 08:08:49 calyopea Exp $
    openhour text;
  BEGIN

  --TODO: materialized view refreshed each hours or halfs OR clever query
  SELECT INTO openhour description
         FROM shop_flat_table
        WHERE shop_id IN (select id from workers where shop=shopName)
          AND flat_txt='openhour';

  IF ( NOT FOUND ) THEN

    RAISE DEBUG 'opening_hour for % is null',shopName;
    RETURN QUERY EXECUTE 'SELECT opened FROM stupid_bool_table WHERE opened=true';   -- by DEFAULT
    -- RAISE EXCEPTION 'cant be here';   -- could be !

  ELSE

    RAISE DEBUG 'opening_hour for % is % (before replace)',shopName,openhour;

    openhour:=REPLACE(openhour,'dow', extract(dow  from NOW())::text);
    openhour:=REPLACE(openhour,'hour',extract(hour from NOW())::text);

    RAISE DEBUG 'opening_hour for % is % (after replace)',shopName,openhour;

    RETURN QUERY EXECUTE 'SELECT opened FROM stupid_bool_table WHERE opened=' || openhour;

  END IF;

END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 100;

So now: with the data :

shop  | opening_hours
------+------------------------------------------------------
ShopA | ((dow between 1 and 5) and (hour between 9 and 16)))
ShowB | ((dow between 1 and 5) and (hour between 9 and 17)))

SELECT * FROM grab_worker_test_opening_hour('ShopB');
psql:batch_workers.psql:124: DEBUG:  opening_hour for ShopB is ((dow between 1 and 5) and (hour between 9 and 17)) OR (dow in (6,7)) (before replace)
psql:batch_workers.psql:124: DEBUG:  opening_hour for ShopB is ((3 between 1 and 5) and (17 between 9 and 17)) OR (3 in (6,7)) (after replace)
 opened
--------
 t
(1 ligne)

(and opened=f for shopA at the same time: 2018-07-25 17:15:00 (iso time)).

urbain
  • 1
  • 2