[MYSQL] genereren van een date range

[Verwijderd]

Ik heb een agenda table, met daarin events die bijv maandelijks, wekelijks of dagelijks worden herhaald.

event_id	date_start	date_end	event_repeat
1	2010-03-13	2010-07-20	weekly

Omdat ik dus voor een event dat bijv dagelijks herhaald wordt geen 365 rows wil toevoegen aan de db wil ik dus dat wanneer ik een SELECT query doe, dat ik dan voor elke dag in de periode tussen date_start en date_end een result row teruggegeven wordt.

Nu heb ik de onderstaande query gevonden, hier kan ik dagen mee genereren tussen een bepaalde periode, en dus is me probleem mbt de dagelijks herhaalde events opgelost. maar nu wil ik deze query ook gebruiken voor de wekelijkse herhalingen, dus om de 7 dagen, alleen om deze query aan te kunnen passen, moet ik deze query begrijpen, maar ik begrijp eerlijk gezegd niet echt wat er gebeurd.

SQL:

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2010-01-20' and '2010-8-24'

Of eventueel andere suggesties hoe dit helemaal anders te programmeren is? En nee, een for/while/loop in php is geen oplossing...

[ACM]

Je genereert alle getallen tussen 0 en 999 door een crossjoin van [0, ..., 9] met [0, ..., 9] en met [0, ..., 9] te maken (dus je maakt "0, 0, 1", "0, 0, 2" en "5, 7, 4" etc) en dan doe je a + 10b + 100c om er een getal van te maken.

Vervolgens gebruik je dat gecombineerde getal als een X bij dag-interval voor de berekening "vandaag - X dagen". Dat wordt bereikt met de 'curdate() - INTERVAL ... DAY'.

Let er wel op dat deze query alleen werkt met de afgelopen bijna 3 jaar (vandaag tot vandaag - 999 dagen). Als je een ander datumbereik zou willen, zou je de curdate moeten veranderen door een normale datum ergens in het verleden.

Voor wekelijks, maandelijks, etc kan je vrij triviaal de " - INTERVAL ... DAY" aanpassen naar "- INTERVAL ... WEEK" of uiteraard MONTH. Let er wel op dat je met bovenstaande query niet de eerste van de week/maand neemt, waardoor met name bij maanden het nog wel eens onverwachte resultaten kan opleveren (op 31 oktober - 1 maand doen geeft je in theorie 31 september).

[DataGhost]

Kan je niet beter een query doen op alle events waar je zoek-interval overlapt het het event-interval (hint: niet niet is makkelijker dan wel) en vervolgens in je programmalogica kijken wat je ermee moet doen? Gewoon eenmalig die query doen en dan een forloopje met alle dagen. Je kan bijv. voor 'wekelijks' kijken wat voor dag de start van het event-interval is en kijken of de 'huidige' dag eenzelfde is. Analoog voor maandelijks.

[ Voor 4% gewijzigd door DataGhost op 08-11-2010 09:31 ]

[Janoz]

Verwijderd schreef op maandag 08 november 2010 @ 08:45:
...Omdat ik dus voor een event dat bijv dagelijks herhaald wordt geen 365 rows wil toevoegen aan de db....

Waarom niet als ik vragen mag?

[ACM]

Janoz schreef op maandag 08 november 2010 @ 10:05:
Waarom niet als ik vragen mag?

Volgens mij wordt het over het algemeen als een bad practice gezien om records voor alle beschikbare dagen in het jaar toe te voegen. Bovendien moet je dan ook nog rekening houden met schrikkeljaar enzo en dat een dag niet elkaar dezelfde is (1 januari is niet altijd een maandag). Het via zo'n query oplossen is natuurlijk ook niet ideaal, maar vrijwaart je wel van al dat soort details.

Domweg de gegevens opvragen en daarna in je applicatie de gaten in je bereik opvullen kan natuurlijk ook handig werken.

[Janoz]

Ik bedoel niet alle dagen opslaan om in je query te gebruiken, maar voor elk voorkomen van een herhalende afspraak een entry toevoegen aan je tabel. Gewoon een 1:n relatie ipv 1:1 relatie tussen een afspraak en een moment. Dat maakt het inserten misschien wel iets duurder, maar het uitlezen wordt ontiegelijk veel simpeler. Als je daarbij meeneemt dat een agenda over het algemeen veel vaker uitgelezen wordt dan gewijzigd dan is dat een goed te verdedigen punt.

Vandaar dus mijn vraag. Waarom zou je niet elk voorkomen los opslaan? Ik vermoed namelijk dat die keuze gemaakt is op basis van onterechte veronderstellingen mbt de efficientie van de implementatie.

[ACM]

Ah, dat is op zich wel handiger ja. Heeft sowieso als voordeel dat je individuele afspraken kunt verzetten/overslaan, zonder de hele reeks te moeten aanpassen en/of zonder dat je het verleden hoeft aan te passen.

[Janoz]

Precies. Je legt het meer ingewikkeldere deel nu bij het bewerken van een afspraak ipv bij het uitlezen. En bij het bewerken wordt het een stuk makkelijker aangezien dit een standaard 1:n relatie is. Het ingewikkeldere deel is meer het uitrekenen van de datum waarop de repeterende afspraak valt, maar dat is enkel eenmalig en voor maar 1 afpsraak tegelijk. Je zult alleen moeten bepalen tot hoever je die afspraak vooruit gaat plannen, maar dat kun je ook in je GUI oplossen (met een verplichte einddatum). Het enige nadeel is dat het iets meer ruimte in beslag neemt (paar bytes per voorkomen), maar dat is verwaarloosbaar tov de voordelen.

[Verwijderd]

Janoz schreef op maandag 08 november 2010 @ 13:13:
Precies. Je legt het meer ingewikkeldere deel nu bij het bewerken van een afspraak ipv bij het uitlezen. En bij het bewerken wordt het een stuk makkelijker aangezien dit een standaard 1:n relatie is. Het ingewikkeldere deel is meer het uitrekenen van de datum waarop de repeterende afspraak valt, maar dat is enkel eenmalig en voor maar 1 afpsraak tegelijk. Je zult alleen moeten bepalen tot hoever je die afspraak vooruit gaat plannen, maar dat kun je ook in je GUI oplossen (met een verplichte einddatum). Het enige nadeel is dat het iets meer ruimte in beslag neemt (paar bytes per voorkomen), maar dat is verwaarloosbaar tov de voordelen.

hmm, ja inderdaad.

het zal toch bijna niet (nooit) voorkomen dat iemand een event toevoegt dat dagelijks herhaald wordt voor een periode van 10 jaar.

denk inderdaad dat ik weer te moeilijk heb gedacht, en dat de voor de hand liggende oplossing toch beter is.