SQL Opslaan van mysql interval in de database.

Pagina: 1
Acties:

Onderwerpen

Vraag


  • Uhmmie
  • Registratie: Januari 2000
  • Laatst online: 28-06 10:32
Is het mogelijk om een mysql interval op te slaan in de database?
Die ik daarna direct weer kan gebruiken in een query?
Het idee is het volgende in een sql functie wil ik iets doen als:
SELECT DATE_ADD(some_date_time_col, INTERVAL -10 HOUR) FROM some_table;
Ik wil echter dat die INTERVAL -10 HOUR uit een andere kolom komt, dus iets als:
SELECT DATE_ADD(some_date_time_col, interval_col) FROM some_table;
Nu snap ik dat alles terug kan rekenen naar secondes en daarmee werken, maar dat geeft soms een kleine afwijking.
De vraag is dus nu hoe ik een interval (inclusief unit) het beste kan opslaan in de database?
Zodat ik hem nu direct daarna in een SQL functie (zoals DATE_ADD) kan gebruiken?
Ik heb al een flinke tijd zitten googlen, maar ik kan helaas geen oplossing vinden.

Currently playing: MTG Arena (PC)

Alle reacties


  • DJMaze
  • Registratie: Juni 2002
  • Niet online
Uhmmie schreef op woensdag 18 september 2019 @ 11:19:
Ik heb al een flinke tijd zitten googlen, maar ik kan helaas geen oplossing vinden.
Je google string klopt niet.

Met https://www.google.com/se..._ADD+interval+from+column
is mijn eerste antwoord: https://stackoverflow.com/a/22404258

Maak je niet druk, dat doet de compressor maar


  • Uhmmie
  • Registratie: Januari 2000
  • Laatst online: 28-06 10:32
Ik was die inderdaad ondertussen zelf ook tegen gekomen. Ik had eigenlijk gehoopt dat ik niet voor elke optie een check op een stringwaarde hoefde uit te voeren, maar dat er een soort evaluate voor keywords was, of dat je een dateinterval in zijn geheel op kon slaan.. Maar helaas zijn beide mogelijkheden niet aanwezig.

Currently playing: MTG Arena (PC)


Acties:
  • +1 Henk 'm!

  • RobIII
  • Registratie: December 2001
  • Niet online

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

(overleden)
Uhmmie schreef op woensdag 18 september 2019 @ 11:19:
Nu snap ik dat alles terug kan rekenen naar secondes en daarmee werken, maar dat geeft soms een kleine afwijking.
Kun je daar iets meer over uitweiden? Wat zijn precies je requirements dan?

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Je eigen tweaker.me redirect

Over mij


Acties:
  • 0 Henk 'm!

  • DJMaze
  • Registratie: Juni 2002
  • Niet online
Je hebt waarschijnlijk wel problemen met month en year.
31 januari + 1 month = ....
29 februari + 1 year = ....

Dus wat @RobIII zegt is wel belangrijk

[ Voor 16% gewijzigd door DJMaze op 20-09-2019 07:50 ]

Maak je niet druk, dat doet de compressor maar


Acties:
  • 0 Henk 'm!

  • ACM
  • Registratie: Januari 2000
  • Niet online

ACM

Software Architect

Werkt hier

DJMaze schreef op vrijdag 20 september 2019 @ 07:48:
Je hebt waarschijnlijk wel problemen met month en year.
31 januari + 1 month = ....
29 februari + 1 year = ....

Dus wat @RobIII zegt is wel belangrijk
Zelfs een dag kan uitmaken. We hebben er tenslotte vrij recent eentje gehad van 1 seconde langer dan normaal en met zomer/wintertijd kan het ook relevant zijn om '1 dag' ipv '86400 seconde' op te tellen.
Uhmmie schreef op donderdag 19 september 2019 @ 10:25:
Ik was die inderdaad ondertussen zelf ook tegen gekomen. Ik had eigenlijk gehoopt dat ik niet voor elke optie een check op een stringwaarde hoefde uit te voeren, maar dat er een soort evaluate voor keywords was, of dat je een dateinterval in zijn geheel op kon slaan.. Maar helaas zijn beide mogelijkheden niet aanwezig.
Op zich kan je ook zoiets doen:
SQL:
1
SELECT theDate + INTERVAL theYears YEAR + INTERVAL theMonths MONTH + ...

Die waardes mogen gewoon 0 zijn en met de + is het nog redelijk leesbaar (ivt geneste date_add's).

Nadeel is dan natuurlijk dat je voor elke gradatie dan een losse kolom moet opslaan. Voordeel is dat je dan wel mooier "1 jaar, 3 dagen en 5 minuten" kan opslaan :P

Als je nog kan switchen van database; PostgreSQL heeft wel een interval field ;)

Acties:
  • 0 Henk 'm!

  • DJMaze
  • Registratie: Juni 2002
  • Niet online
ACM schreef op vrijdag 20 september 2019 @ 08:27:
en met zomer/wintertijd kan het ook relevant zijn om '1 dag' ipv '86400 seconde' op te tellen.
Als je problemen hebt met zomer/wintertijd klopt je code inderdaad niet.
Het is niet voor niks dat DATETIME in UTC wordt opgeslagen.

Maak je niet druk, dat doet de compressor maar


Acties:
  • 0 Henk 'm!

  • Uhmmie
  • Registratie: Januari 2000
  • Laatst online: 28-06 10:32
RobIII schreef op donderdag 19 september 2019 @ 10:46:
[...]

Kun je daar iets meer over uitweiden? Wat zijn precies je requirements dan?
Requirements zijn dat door een extern proces update intervallen door gegeven worden. Deze zijn wel altijd maar in 1 eenheid (dus 10 seconde, of 3 maanden of 2 jaar). Deze worden opgeslagen in een tabel en kunnen ook regelmatig veranderen. Aan de hand van deze tabel moeten andere tabellen ge-update worden dmv een identifier. Ik wil dit echter afhandelen in sql (en niet met een tussenstap in bijvoorbeeld php.
ACM schreef op vrijdag 20 september 2019 @ 08:27:
[...]

Zelfs een dag kan uitmaken. We hebben er tenslotte vrij recent eentje gehad van 1 seconde langer dan normaal en met zomer/wintertijd kan het ook relevant zijn om '1 dag' ipv '86400 seconde' op te tellen.


[...]


Op zich kan je ook zoiets doen:
SQL:
1
SELECT theDate + INTERVAL theYears YEAR + INTERVAL theMonths MONTH + ...

Die waardes mogen gewoon 0 zijn en met de + is het nog redelijk leesbaar (ivt geneste date_add's).

Nadeel is dan natuurlijk dat je voor elke gradatie dan een losse kolom moet opslaan. Voordeel is dat je dan wel mooier "1 jaar, 3 dagen en 5 minuten" kan opslaan :P

Als je nog kan switchen van database; PostgreSQL heeft wel een interval field ;)
toon volledige bericht
Het is helaas een aanpassing voor een bestaand project waarbij de database niet aangepast kan worden op een kort termijn. Ik houd je oplossing iig nog wel in gedachte.

Currently playing: MTG Arena (PC)


Acties:
  • 0 Henk 'm!

  • RobIII
  • Registratie: December 2001
  • Niet online

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

(overleden)
Uhmmie schreef op zondag 22 september 2019 @ 21:35:
Requirements zijn dat door een extern proces update intervallen door gegeven worden. Deze zijn wel altijd maar in 1 eenheid (dus 10 seconde, of 3 maanden of 2 jaar). Deze worden opgeslagen in een tabel en kunnen ook regelmatig veranderen. Aan de hand van deze tabel moeten andere tabellen ge-update worden dmv een identifier. Ik wil dit echter afhandelen in sql (en niet met een tussenstap in bijvoorbeeld php.
Dan zie ik nog steeds geen reden om geen interval (in secondes of millisecondes of minuten of uren of whatever je wil) te kunnen gebruiken en al helemaal niet wat dat te maken heeft met "een kleine afwijking" waar je 't over had :?

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Je eigen tweaker.me redirect

Over mij


Acties:
  • 0 Henk 'm!

  • Uhmmie
  • Registratie: Januari 2000
  • Laatst online: 28-06 10:32
RobIII schreef op zondag 22 september 2019 @ 21:39:
[...]

Dan zie ik nog steeds geen reden om geen interval (in secondes of millisecondes of minuten of uren of whatever je wil) te kunnen gebruiken en al helemaal niet wat dat te maken heeft met "een kleine afwijking" waar je 't over had :?
Omdat over 1 maand in januari meer secondes zijn dan over 1 maand in februari. En aangezien die over 1 maand in alle gevallen letterlijk over 1 maand moet zijn (en niet over 28.29,30 of 31 dagen), kan ik dus niet zeggen over x seconde. Want dat is niet voor alle rijen die ik moet gaan updaten gelijk.

Currently playing: MTG Arena (PC)


Acties:
  • 0 Henk 'm!

  • DJMaze
  • Registratie: Juni 2002
  • Niet online
Uhmmie schreef op zondag 22 september 2019 @ 21:47:
En aangezien die over 1 maand in alle gevallen letterlijk over 1 maand moet zijn (en niet over 28.29,30 of 31 dagen), kan ik dus niet zeggen over x seconde. Want dat is niet voor alle rijen die ik moet gaan updaten gelijk.
En wat is 1 maand?
20 januari + 1 maand = 20 februari

30 januari + 1 maand = 2 maart?

Vertel dat nou eens, want je bent niet duidelijk daarin. Wij zeggen dat niet voor niks.

Ga anders voor de solar hijri kalender, daarin zijn de maanden gelijk... (Of toch niet?)

[ Voor 7% gewijzigd door DJMaze op 22-09-2019 23:32 ]

Maak je niet druk, dat doet de compressor maar


Acties:
  • 0 Henk 'm!

  • Uhmmie
  • Registratie: Januari 2000
  • Laatst online: 28-06 10:32
DJMaze schreef op zondag 22 september 2019 @ 23:21:
[...]

En wat is 1 maand?
20 januari + 1 maand = 20 februari

30 januari + 1 maand = 2 maart?

Vertel dat nou eens, want je bent niet duidelijk daarin. Wij zeggen dat niet voor niks.

Ga anders voor de solar hijri kalender, daarin zijn de maanden gelijk... (Of toch niet?)
Indien de nieuwe datum niet bestaat dan moet het de laatste dag van de nieuwe maand worden (dus idd 30 januari wordt 28 februari bij een normaal jaar en 29 februari bij een schrikkeljaar). In alle andere gevallen wordt het dus 1 Jan = 1 feb. 20 april = 20 mei, etc, etc

Currently playing: MTG Arena (PC)


Acties:
  • 0 Henk 'm!

  • ACM
  • Registratie: Januari 2000
  • Niet online

ACM

Software Architect

Werkt hier

DJMaze schreef op zondag 22 september 2019 @ 23:21:
Vertel dat nou eens, want je bent niet duidelijk daarin. Wij zeggen dat niet voor niks.
Waarom maakt het je zoveel uit als hij zegt dat in seconden opslaan niet werkt? 't Is toch algemeen bekend dat maanden niet even lang zijn?
Dat er vervolgens ook problemen zijn op te lossen bij datums vanaf de 29e is helemaal waar, maar als je een vaste hoeveelheid seconde kiest ter representatie van 1 maand doe je afhankelijk van je keuze gemiddeld genomen minimaal de helft van je berekeningen fout ;)

Overigens ben ik wel met de suggestie hier en daar eens dat 'een kleine afwijking' een gekke naam is voor 'een of meerdere dagen' :P

Acties:
  • 0 Henk 'm!

  • Uhmmie
  • Registratie: Januari 2000
  • Laatst online: 28-06 10:32
ACM schreef op maandag 23 september 2019 @ 07:52:
[...]

Waarom maakt het je zoveel uit als hij zegt dat in seconden opslaan niet werkt? 't Is toch algemeen bekend dat maanden niet even lang zijn?
Dat er vervolgens ook problemen zijn op te lossen bij datums vanaf de 29e is helemaal waar, maar als je een vaste hoeveelheid seconde kiest ter representatie van 1 maand doe je afhankelijk van je keuze gemiddeld genomen minimaal de helft van je berekeningen fout ;)

Overigens ben ik wel met de suggestie hier en daar eens dat 'een kleine afwijking' een gekke naam is voor 'een of meerdere dagen' :P
Als je met secondes voor 30,5 dag rekent heb je een halve dag afwijking (muv februari). Dat is op een maand geen mega afwijking, maar in mijn geval is het helaas teveel. Dat is dan dus een gemiddeld een afwijking van een kleine 2%. Ik denk dat ik voor nu maar even met de switch statement blijf werken en dan ga kijken of we nu naar postgres of iets dergelijk kunnen migreren aangezien die date interval op steeds meer plaatsen in mijn projectje terug gaat komen.

Currently playing: MTG Arena (PC)


Acties:
  • 0 Henk 'm!

  • CurlyMo
  • Registratie: Februari 2011
  • Laatst online: 22:55
Uhmmie schreef op maandag 23 september 2019 @ 06:52:
[...]

Indien de nieuwe datum niet bestaat dan moet het de laatste dag van de nieuwe maand worden (dus idd 30 januari wordt 28 februari bij een normaal jaar en 29 februari bij een schrikkeljaar). In alle andere gevallen wordt het dus 1 Jan = 1 feb. 20 april = 20 mei, etc, etc
Gaat dit op voor alle resoluties of alleen voor maanden?
- 31 december 2019 + 370 dagen wordt 31 december 2020
- 30 januari + 30 dagen wordt 28 februari
- 11:59 + 70 minuten wordt 12:59
- 11:50:40 + 20 seconden wordt 11:50:59

Sinds de 2 dagen regel reageer ik hier niet meer


Acties:
  • 0 Henk 'm!

  • DJMaze
  • Registratie: Juni 2002
  • Niet online
Uhmmie schreef op maandag 23 september 2019 @ 06:52:
Indien de nieuwe datum niet bestaat dan moet het de laatste dag van de nieuwe maand worden (dus idd 30 januari wordt 28 februari bij een normaal jaar en 29 februari bij een schrikkeljaar).
Dus: 30 januari => 28 februari => 28 maart
Of moet ie dan weer automagisch naar 30 maart?

Maak je niet druk, dat doet de compressor maar


  • Uhmmie
  • Registratie: Januari 2000
  • Laatst online: 28-06 10:32
DJMaze schreef op maandag 23 september 2019 @ 10:26:
[...]

Dus: 30 januari => 28 februari => 28 maart
Of moet ie dan weer automagisch naar 30 maart?
Dan mag het gewoon 28 maart blijven.
CurlyMo schreef op maandag 23 september 2019 @ 08:27:
[...]

Gaat dit op voor alle resoluties of alleen voor maanden?
- 31 december 2019 + 370 dagen wordt 31 december 2020
- 30 januari + 30 dagen wordt 28 februari
- 11:59 + 70 minuten wordt 12:59
- 11:50:40 + 20 seconden wordt 11:50:59
Nee, plus 370 dagen is gewoon + 370 dagen. 70 minuten = 70 minuten.

Currently playing: MTG Arena (PC)


  • CurlyMo
  • Registratie: Februari 2011
  • Laatst online: 22:55
Uhmmie schreef op zaterdag 28 september 2019 @ 11:07:
[...]
Nee, plus 370 dagen is gewoon + 370 dagen. 70 minuten = 70 minuten.
Maar als +370 dagen toch op 29 februari terecht komt in een niet-schrikkeljaar dan moet het wel 28 februari worden en niet 1 maart?

Sinds de 2 dagen regel reageer ik hier niet meer


  • ACM
  • Registratie: Januari 2000
  • Niet online

ACM

Software Architect

Werkt hier

CurlyMo schreef op zaterdag 28 september 2019 @ 11:23:
[...]

Maar als +370 dagen toch op 29 februari terecht komt in een niet-schrikkeljaar dan moet het wel 28 februari worden en niet 1 maart?
Waarom wordt er zo moeilijk gedaan over zijn vraag?

Uiteindelijk wil hij dit effect kunnen bereiken:
SQL:
1
2
3
select date + interval 370 days;
select date + interval 3 months;
select date + interval 70 minutes;


Etc. Maar die intervallen (dus '370 days') wil ie opslaan in de database. En omdat normaliseren naar één tijdseenheid niet gegarandeerd hetzelfde resultaat geeft (dus eigenlijk niet mogelijk is) wil hij zowel het cijfer als de tijdseenheid opslaan.

Hoe de berekening daarna verder precies gebeurt is dan eigenlijk niet meer relevant, want dat is verder hoe MySQL dat nu al doet.
SQL:
1
2
3
4
5
6
7
8
9
select '2019-01-31' + interval 1 month ;
select  '2019-01-31' + interval 2 month;
select '2019-01-31' + interval 30.5 day;
select '2019-01-31' + interval 61 day;
-- geeft:
2019-02-28  
2019-03-31
2019-03-03
2019-04-02

[ Voor 5% gewijzigd door ACM op 28-09-2019 11:38 ]


  • CurlyMo
  • Registratie: Februari 2011
  • Laatst online: 22:55
ACM schreef op zaterdag 28 september 2019 @ 11:34:
[...]

Waarom wordt er zo moeilijk gedaan over zijn vraag?
Hoe de berekening daarna verder precies gebeurt is dan eigenlijk niet meer relevant, want dat is verder hoe MySQL dat nu al doet.
De vraag op zich is inderdaad niet complex. Het enige wat hier wordt uitgezocht of het standaard gedrag van MySQL past bij wat TS zoekt. Zo ja, dan prima. Zijn we snel klaar.
Etc. Maar die intervallen (dus '370 days') wil ie opslaan in de database. En omdat normaliseren naar één tijdseenheid niet gegarandeerd hetzelfde resultaat geeft (dus eigenlijk niet mogelijk is) wil hij zowel het cijfer als de tijdseenheid opslaan.
Dat had iedereen hier inderdaad al door :)

Sinds de 2 dagen regel reageer ik hier niet meer


  • DJMaze
  • Registratie: Juni 2002
  • Niet online
Er is wel een ISO 8601 period/duration P1Y1DT1H enzo, maar daar snapt mysql volgensmij nog niks van.
Zoekmachines hebben daar vast een oplossing voor zodat TS kan normaliseren met de ISO die ook door iCalendar RFC en PHP wordt gebruikt.

Maak je niet druk, dat doet de compressor maar


Acties:
  • 0 Henk 'm!

  • P.O. Box
  • Registratie: Augustus 2005
  • Niet online
Uhmmie schreef op woensdag 18 september 2019 @ 11:19:
Is het mogelijk om een mysql interval op te slaan in de database?
Die ik daarna direct weer kan gebruiken in een query?
Het idee is het volgende in een sql functie wil ik iets doen als:
SELECT DATE_ADD(some_date_time_col, INTERVAL -10 HOUR) FROM some_table;
Ik wil echter dat die INTERVAL -10 HOUR uit een andere kolom komt, dus iets als:
SELECT DATE_ADD(some_date_time_col, interval_col) FROM some_table;
Nu snap ik dat alles terug kan rekenen naar secondes en daarmee werken, maar dat geeft soms een kleine afwijking.
De vraag is dus nu hoe ik een interval (inclusief unit) het beste kan opslaan in de database?
Zodat ik hem nu direct daarna in een SQL functie (zoals DATE_ADD) kan gebruiken?
Ik heb al een flinke tijd zitten googlen, maar ik kan helaas geen oplossing vinden.
Het voegt niet heel veel toe aan het topic, maar het is denk ik toch je enige mogelijkheid als ik zo nadenk/google door het uit te schrijven per case met een switch. Je kunt het natuurlijk wel in een mysql-function gooien, als je die mogelijkheid wel hebt.

SQL:
1
2
3
4
5
6
7
8
9
10
11
set @dbvalue := "1 month";

select
SUBSTRING_INDEX(@dbvalue, " ", 1) ,
SUBSTRING_INDEX(@dbvalue, " ", -1) ,
case SUBSTRING_INDEX(@dbvalue, " ", -1) 
when 'month' then DATE_ADD(NOW(), interval substring_index(@dbvalue, " ", 1) MONTH)
when 'day'   then date_add(NOW(), interval substring_index(@dbvalue, " ", 1) DAY)
when 'year'  then date_add(NOW(), interval substring_index(@dbvalue, " ", 1) YEAR)
else 'unknown'
end 


werkt ook voor negatieve getallen of gedwongen + ervoor.

edit: eigenlijk wat @DJMaze in zijn eerste reactie al linkte :9

edit 2: ik begreep overigens dat de interval data door een externe applicatie wordt weggeschreven in de database. Ontkom je dan sowieso niet aan een switch statement omdat die externe applicatie niet exact de juiste termen gebruikt als MONTH, DAY, YEAR etc... maar bijvoorbeeld DAYS gebruikt ipv DAY?

[ Voor 22% gewijzigd door P.O. Box op 01-10-2019 17:25 ]

Pagina: 1