some time ago I wrote a small document about MySQL’s COALESCE usage. Today I was asked to help with such a construct for a similar usage-scenario. Here’s the result:
If you’re new to this, read my first article first. It will make some things more obvious. You can read it here.
Basically we’re having two tables. The first stores when a shop has opened (ls_Oeffnungszeiten) and the second stores when a shop has closed (ls_Oeffnungszeiten_spezial) – Don’t worry about the german names for the tables and fields, wasn’t my idea and I’ll translate them in the following texts (Oeffnungszeiten = Opening times, spezial = special).
FE (id used to select the shop / distributor) tstamp (some timestamp, not useful for us) tag (tag = day) anfangszeit (anfangszeit = start-time, timestamp) endzeit (endzeit = end-time, timestamp) geschlossen (geschlossen = closed, 16 for closed, 17 for opened) bestellanmahne (wrongly written, should be bestellannahme, and means the time - opening time till someone can buy something (doesn't make sense to buy something 3 minutes before the shop closes, if they need 30 minutes to cook or ship it)
FE (id used to select the shop / distributor) tstamp (some timestamp, not useful for us) anfangsdatum (anfangsdatum = start-date, timestamp) enddatum (enddatum = end-date, timestamp) geschlossen (geschlossen = closed, 16 for closed, 17 for opened)
The field „geschlossen“ in table2 might seem useless to you, because everything in table2 in fact means closed; however, for the COALESCE it will help; COALESCE works by using two fields, so if i use it, it has to have the same field in both tables. Thus i added „geschlossen“ in table 2 with a default value of 16 (which means closed).
Some simple queries:
To get the distributors which have opened, we’d do:
SELECT * FROM table1 WHERE geschlossen = '17'
Let’s make it a bit more complicated, to get the distributors which have opened RIGHT NOW, we’d do:
WHERE WEEKDAY( NOW( ) ) = a.tag -1 AND CURTIME( ) > a.anfangzeit AND CURTIME( ) < ( a.endzeit - a.bestellanmahme )
WEEKDAY gives us a DAY of the WEEK starting at 0, thus we need to do -1. Rest should be clear i guess. Let's take a look at the second table. Basically we've got a range from-to when the shop has closed. So to see when the shop has closed we'd do:
SELECT * FROM table2 WHERE ( UNIX_TIMESTAMP() BETWEEN anfangsdatum AND enddatum )
Quite logical to asssume, that we need to use NOT BETWEEN to get the times a shop has opened; and as those will nearly always return NULL our COALESCE will just work fine. Because as we know from my previous COALESCE article, COALESCE selects the first NOT NULL field.
So let's put it all together and let's create a view out of it:
CREATE VIEW ls_Offen AS SELECT a.FE, COALESCE( b.geschlossen, a.geschlossen ) AS `status` FROM ls_Oeffnungszeiten AS a LEFT JOIN ls_Oeffnungszeiten_spezial AS b ON ( UNIX_TIMESTAMP( ) BETWEEN b.anfangsdatum AND b.enddatum ) AND a.FE = b.FE WHERE WEEKDAY( NOW( ) ) = a.tag -1 AND CURTIME( ) > a.anfangzeit AND CURTIME( ) < ( a.endzeit - a.bestellanmahme )
This will return two fields (FE) the distributor id and status -> which is either 16 or 17. So you just need to query that view, to see which distributors have opened.
Maybe I should look for a Job as MySQL-Query-Designer *cough*