MySQL: Weekdays and BOOLs
Message boards :
Cafe SETI :
MySQL: Weekdays and BOOLs
Message board moderation
| Author | Message |
|---|---|
|
N/A Send message Joined: 18 May 01 Posts: 3718 Credit: 93,649 RAC: 0 |
[font='courier,courier new']OK, so this is way off topic, but I need brains -- I mean geniuses, not just their brains -- to help out, and where do you find bigger... uhm, brains?... than the SETI Café, right? 'course I'm right. Here goes: I've got a MySQL table for scheduling purposes. I need to be able to flag what days of the week an event occurs. Part of the statement is here: CREATE TABLE Schedules(      ...      Time_Start TIME, Time_End TIME,      Date_Start DATE, Date_End DATE,      Weekday_Sunday BOOL, Weekday_Monday BOOL, ... , Weekday_Saturday BOOL,      ...); The idea was that I would be able to query and get the records that apply to any given moment, such as... SELECT * FROM Schedules WHERE      (Time_Start <= NOW() AND NOW() < Time_End) AND (Date_Start <= NOW() AND NOW() < Date_End) ... ...but how would I say "AND where the current day of the week contains TRUE"? First one with a good, functional answer gets a decaf in the Café on me; Elegant, espresso.[/font] ID: 149529 ·  |
 Fuzzy Hollynoodles  Send message Joined: 3 Apr 99 Posts: 9659 Credit: 251,998 RAC: 0 |
SELECT * FROM Schedules WHERE Â Â Â Â Â (Time_Start <= NOW() AND NOW() < Time_End) AND (Date_Start <= NOW() AND NOW() < Date_End) and ((now()=weekday_sunday and (weekday_sunday=true)) or (now()=weekday_monday and (weekday_monday=true)) or (now()=weekday_tuesday and (weekday_tuesday=true)) or (.... etc... )) ... Looks very clumsy! Check your mail! And save your decaf!  "I'm trying to maintain a shred of dignity in this world." - Me 
ID: 149649 · |
Captain Avatar  Send message Joined: 17 May 99 Posts: 15133 Credit: 529,088 RAC: 0 
|
Way to go Fuzzy you show em! 
ID: 149674 · |
|
Fuzzy Hollynoodles Send message Joined: 3 Apr 99 Posts: 9659 Credit: 251,998 RAC: 0 |
Yes, a good database design is alpha and omega to all database work! NA should know that! ;-) "I'm trying to maintain a shred of dignity in this world." - Me
ID: 149678 · |
|
N/A Send message Joined: 18 May 01 Posts: 3718 Credit: 93,649 RAC: 0 |
[font='courier,courier new']Sorry, but the design is 3NF: Each weekday is independent of each other, and specific to each record. I'm not about to make a new 127-record table and have SQL do bit-twiddling. :-( That's one decaf so far - Anyone else wanna take a shot?[/font] ID: 149705 · |
|
Fuzzy Hollynoodles Send message Joined: 3 Apr 99 Posts: 9659 Credit: 251,998 RAC: 0 |
[font='courier,courier new']Sorry, but the design is 3NF: Each weekday is independent of each other, and specific to each record. Well, excuse moi! I actually had a design on 3NF for you, but help yourself! "I'm trying to maintain a shred of dignity in this world." - Me
ID: 149737 · |
Colossus  Send message Joined: 8 Jul 05 Posts: 73 Credit: 23,618 RAC: 0
|
I would make a Weekday column which contains an integer value of 1 to 7. Then construct a SQL query that uses a function similar to Weekday(dateValue) in VB or other languages. If the function doesn't exist in MySQL's version of SQL, you could write the function. .. in fact, you could write a function like that which uses the seven boolean columns in your current design. This is the voice of world control. I bring you peace. It may be the peace of plenty and content or the peace of unburied dead. The choice is yours. ID: 149901 · |
|
N/A Send message Joined: 18 May 01 Posts: 3718 Credit: 93,649 RAC: 0 |
[font='courier,courier new']@ Fuzzy I didn't mean it that way - I meant that all non-FKs are independent of each other and relate to the PK for only one record. @ David I can't just flag one day of the week - I need all seven to have T/Fs. It also has to be as platform-independent as possible, so VB-/Apple-/Bash-/Java-scripts are all out of the question. @ * I'm not bashing either possibility, but I'm trying to avoid reinterpreting a query or query result in order to get the data. I'm convinced that a kludge can be avoided. Of course, it'd be much easier if I could TYPE RECORD_ENTRY: ... : Weekday(1 TO 7) AS BOOLEAN : ... : END TYPE, but then I'd be stuck firmly in VB/Windows (and I'm not bailing out on Mac OS, BeOS, Linux, and Unix just yet)... Looks like it's two decafs now.[/font] ID: 149989 · |
|
Fuzzy Hollynoodles Send message Joined: 3 Apr 99 Posts: 9659 Credit: 251,998 RAC: 0 |
[font='courier,courier new']@ Fuzzy Ok, no offense taken then! But did you check your mail? I offered you a solution which meets the requirements of Boyce-Codd/3NF, where you can fill in the weekday for a given post by taking the primary key from a weekday table as a foreign key in your schedules table. By doing this, you avoid redundant data, booleans, and you can get a weekday name to test on in your query! (I guess the weekday name is a part of the timestamp you seem to test it against) I'm not sure the query I gave you is right, as I'm kind of rusty in the inner joins, but I'm sure you can work it out! Else, you have my mail adress! Have fun! Databases are my "guilty pleasures"! .oO(Talking about not having a life, hmmm....!) And, BTW, you're right about keeping it strictly in MySQL, as your can use the database in any environment through ODBC. Always a good idea! "I'm trying to maintain a shred of dignity in this world." - Me
ID: 150005 · |
|
Colossus Send message Joined: 8 Jul 05 Posts: 73 Credit: 23,618 RAC: 0
|
Just used VB as an example for the Weekday function. It is also available on multi-platform programming languages (see Lotus Notes for Win, 3 Unices, Linux, Mac, etc). Perhaps you could write your own function that mimics Weekday(datevalue) ... MyWeekday()? Now I'm wondering if MySQL supports writing your own functions. Stored procedures were just introduced in v5 which is still beta. This is the voice of world control. I bring you peace. It may be the peace of plenty and content or the peace of unburied dead. The choice is yours. ID: 150132 · |
|
N/A Send message Joined: 18 May 01 Posts: 3718 Credit: 93,649 RAC: 0 |
[font='courier,courier new']I thought about making the weekdays into a table, and then referring to the days as a PK/FK relationship, but for that I might as well use a CHAR(1) bit-field and a few boolean ops to get the days-of-week bitmask. For that, though, I'd need something like VB, which in turn, doesn't fly on Mac OS or Linux. (Then again, I don't know the full story on Mono (What a terrible name!), so I might be able to do that (sometime in the future).) Someone suggested to me using a CASE WHEN statement. I've never heard of it before, it's kinda "ON ... GOTO"-esque, but it keeps it all in SQL. I'll be checking it out in the (obscenely huge 1700+ page) MySQL Manual. It's similar to what I'm looking for as far as querying goes, but I'm sure there must be some related kind expression or statement that applies better.[/font] ID: 150288 · |
|
Fuzzy Hollynoodles Send message Joined: 3 Apr 99 Posts: 9659 Credit: 251,998 RAC: 0 |
case when is used like case in c++ and goes like this: CASE [WHEN condition THEN expression]... [ELSE expression] END Example: UPDATE Members SET Phone= (CASE WHEN TYPE = 'Beginner' THEN '45171448' WHEN TYPE = 'Senior' THEN '36144817' ELSE (SELECT Phone FROM Members WHERE No=1) END); Got it??? "I'm trying to maintain a shred of dignity in this world." - Me
ID: 150374 · |
|
N/A Send message Joined: 18 May 01 Posts: 3718 Credit: 93,649 RAC: 0 |
[font='courier,courier new']Right, just like a SWITCH or SELECT/CASE. But I'd still be reprocessing a query. If it were in VB this'd be done already. All I'd have to do is... TYPE Record Times(1 TO 2) AS TIME Dates(1 TO 2) AS DATE Weekday(1 TO 7) AS BOOLEAN END TYPE ...followed by... IF (aRecord.Time(1)<=TIME$ AND TIME$<aRecord.Time(2)) _ AND (aRecord.Date(1)<=DATE$ AND DATE$<aRecord.Date(2)) _ AND aRecord.Weekday(WEEKDAY(WEEKDAY))=TRUE _ THEN ? aRecord END IF ...and that would be the end of that.[/font] ID: 150382 · |
Redshift  Send message Joined: 3 Apr 99 Posts: 122 Credit: 1,244,536 RAC: 0
|
You could have a single column containing the days of the week on which the event may happen. Say it is of type string, max length 7, Possible values are: M (monday) MWF (monday, wednesday, friday) MWFSU (monday, wed, fri, sat, sun) . . . etc. Then, not only can you select where event happens on the current week day-- using string functions-- but it also becomes easy to select events which happen on any set of weekdays you are interested in. SELECT ... WHERE weekday LIKE '%T%U'; (select events that happen on tuesday or sunday). www.onlinetasklist.com ID: 151691 · |
|
N/A Send message Joined: 18 May 01 Posts: 3718 Credit: 93,649 RAC: 0 |
[font='courier,courier new']That's how the data was originally stored. Somewhere after the 10,000th record, queries started slowing down. I had entries like MTW and WMT - Both of which are the same, and yet aren't at the same time. Someone suggested that I try WHERE CASE DAYOFWEEK(CURRENT_DATE) WHEN 1 THEN Weekday_Sunday WHEN 2 THEN Weekday_Monday... WHEN 7 THEN Weekday_Saturday END. It's kludgier, but forces the user to keep the entries cleanly in BOOLs.[/font] ID: 151781 · |
|
Redshift Send message Joined: 3 Apr 99 Posts: 122 Credit: 1,244,536 RAC: 0
|
That's how the data was originally stored. Somewhere after the 10,000th record, queries started slowing down. Is the column indexed? I currently have a similar MySQL table with 2,000,000 rows, and the length of the colum is approx 80 chars, and queries similar to that take approx 3 seconds. Running on AMD 3000+ Approx 3 seconds may be too slow for your application however. www.onlinetasklist.com ID: 153381 · |
|
N/A Send message Joined: 18 May 01 Posts: 3718 Credit: 93,649 RAC: 0 |
Is the column indexed? [font='courier,courier new']I hadn't thought of indexing the DotW column... hmm. Well, either way it seems to be running fine with the CASE statement, and I've managed to hack (used properly here because it isn't a kludge) the most time-conuming column into (you may wanna sit down for this) an Excel =LOOKUP(,,) function. All in all, though: so far, so good. Eventually I'll move it onto a faster box using a RAM disk. (In case you're curious, the raw data is zipped in here. A header-less (and therefore less comprehensible) version comes from this 709KB text file.)[/font] ID: 153396 · |
©2024 University of California
SETI@home and Astropulse are funded by grants from the National Science Foundation, NASA, and donations from SETI@home volunteers. AstroPulse is funded in part by the NSF through grant AST-0307956.