[MYSQL] Sec_to_time en Group By

[Rainmaker1987]

Ik ben bezig met een urenregistratie. Wellicht gaat er zometeen geroepen worden dat ik mijn data niet in date_time velden moet neerzetten maar als UNIX-timestamp. Mij lijkt echter wel dat het volgende moet kunnen:

SQL:

SELECT 
        uren.id_onderdeel, 
        SEC_TO_TIME(
            SUM(
                UNIX_TIMESTAMP(uren.end_date_time) - UNIX_TIMESTAMP(uren.start_date_time)
                )
            ) AS tijd_gewerkt
FROM 
    uren
WHERE 
    end_date_time != ''
GROUP BY uren.id_onderdeel

Hier wordt echter als foutmelding aangegeven dat SEC_TO_TIME niet bestaat als functie. Echter hier bij de makers zelf geven ze aan dat dit het aantal seconden om moet zetten naar een HH:MM:SS format.

Wanneer ik de stukken van uren.id_onderdeel (ook de group by) eruit haal doet de functie het wel. Wanneer dit wordt toegevoegd krijg ik een foutmelding. Ik kan echter nergens iets over deze foutmelding wat vinden.

Doe ik iets fout in de query of is het inderdaad hopeloos en zal ik mijn data moeten gaan opslaan in secondes?

Mijn mysql versie = MySQL 5.0.38 phpMyAdmin 2.6.4-pl2

Ps. Ik kreeg de problemen ook bij DATEDIFF en bij TIMEDIFF deze had ik als eerste geprobeerd aangezien deze speciaal voor date_time velden zijn gemaakt. Maar ook die moesten eerst naar seconden worden omgezet aangezien SUM() niet werkt met tijden.
Ik ben overigens niet de enige die dit blijkbaar heeft 1 , 2
Pps. Dit is overigens niet de volledige query, hij moet nog een viertal andere tabellen meenemen, echter deze versimpelde versie doet het al niet, vandaar dat ik eerst even de basis weergeef

[ Voor 6% gewijzigd door Rainmaker1987 op 27-04-2007 19:54 . Reden: Toevoeging + typo ]

[LauPro]

Het is zeker aan te raden om unix timestamps te gebruiken. Niet alleen vanwege het feit dat deze per definitie GMT zijn (bij DATE/DATETIME is dat altijd maar de vraag in MySQL) maar ook zodat je nooit geklooi kan gaan krijgen met tijdzones of notaties.

Over het algemeen kan je formatting beter niet in de database doen. Dus vraag gewoon het aantal secondes op (hooguit nog het verschil) en presenteer die in je applicatie als zijnde uren/minuten/seconden etc.

[Rainmaker1987]

Helaas een antwoord wat ik liever niet had gezien, maar het is nou eenmaal zo. Blijft bij mij echter nog wel steeds de vraag hangen wat er nou fout aan mijn query is. Is SEC_TO_TIME nou zo'n onstabiele functie? Want het lijkt mij toch ideaal als ik de database direct het omrekenwerk kan laten doen.

[LauPro]

Heb je die voorbeeldqueries van MySQL al geprobeerd? Misschien is MySQL zonder deze extra functies gecompileerd (minimal oid).

SQL:

mysql> SELECT SEC_TO_TIME(2378);
        -> '00:39:38'
mysql> SELECT SEC_TO_TIME(2378) + 0;
        -> 3938

[Rainmaker1987]

Ik heb het inderdaad ook met simpelere query's gebruikt. Zoals ik mn startpost werkte het wel zolang ik de stukken van uren.id_onderdeel (ook de group by) eruit haal.

[LauPro]

Ben het even voor je na gegaan met de volgende query:

SQL:

SELECT  
        uren.onderdeel,  
        SEC_TO_TIME( 
            SUM( 
                UNIX_TIMESTAMP(uren.end) - UNIX_TIMESTAMP(uren.start) 
                ) 
            ) AS tijd_gewerkt 
FROM  
    uren 
WHERE  
    uren.end != '' 
GROUP BY uren.onderdeel

En met de volgende database structure:

SQL:

CREATE TABLE `uren` (
  `id` bigint(20) NOT NULL auto_increment,
  `onderdeel` int(1) NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `uren` (`id`, `onderdeel`, `start`, `end`) VALUES 
(1, 1, '2007-04-28 13:50:19', '2007-04-28 14:50:26'),
(2, 1, '2007-04-28 16:50:57', '2007-04-28 18:51:03'),
(3, 2, '2007-04-28 13:54:20', '2007-04-28 17:54:25');

Dan krijg ik netjes:

code:

+-----------+--------------+
| onderdeel | tijd_gewerkt |
+-----------+--------------+
|         1 | 03:00:13     |
|         2 | 04:00:05     |
+-----------+--------------+

Ook MySQL 5.0.38, zit waarschijnlijk toch iets in je database model?

[Rainmaker1987]

Ik heb het nu nogmaals geprobeerd, ik dacht even dat het er misschien aan zou kunnen liggen dat bij mij de laatste entry altijd de end_date_time leeg is maar dat maakt niets uit.

SQL:

SELECT    
        uren.id_onderdeel,    
        SEC_TO_TIME(   
            SUM(   
                UNIX_TIMESTAMP(uren.end_date_time) - UNIX_TIMESTAMP(uren.start_date_time)   
                )   
            ) AS tijd_gewerkt   
FROM    
    uren   
WHERE    
    uren.end_date_time != ''   
GROUP BY uren.id_onderdeel

Als code en als structuur:

Als inhoud:


Nog steeds krijg ik de foutmelding: #1305 - FUNCTION 10246proje.SEC_TO_TIME does not exist

Net nog even voor de zekerheid de voorbeeldquery's gedaan, maar die komen er braaf uit. Zijn er misschien andere instellingen die anders kunnen zijn?

[frickY]

LauPro schreef op zaterdag 28 april 2007 @ 03:38:
Het is zeker aan te raden om unix timestamps te gebruiken. Niet alleen vanwege het feit dat deze per definitie GMT zijn (bij DATE/DATETIME is dat altijd maar de vraag in MySQL) maar ook zodat je nooit geklooi kan gaan krijgen met tijdzones of notaties.

Het is absoluut niet aan te raden data in seconden op te slaan. MySQL biedt je hele krachtige functies om met datetime's te werken, terwijl je bij timestamps van je eigen ffantasie en rekenvaardigheid afhankelijk bent.

let overigens op dat SEC_TO_TIME een MySQL 5 functie is. De meeste hosters draaien echter nog op een 3.* variant.

[ Voor 5% gewijzigd door frickY op 28-04-2007 14:41 ]

[Rainmaker1987]

Ik had ook het vermoeden dat dit de beste wijze was. Het boek dat ik oa gelezen heb gaf ook niet voor niets aan: laat de database het werk doen wanneer het kan.

Zoals al eerder aangegeven is de database versie 5.0.38-enterprise-gpl-log en zou het dus gewoon moeten doen. De kleine tests wijzen dit gewoon uit.

Voorlopig werk ik maar even zonder de TIME_TO_SEC het berekenen doet hij dan wel gewoon goed en gooit er het aantal seconden uit. Die zet ik zelf dan wel met php om. Maar mocht iemand zien waarom de db weigert mee te werken. Een oplossing zie ik graag tegemoet

[Rainmaker1987]

Wat er veranderd is weet ik niet. De query is een stuk uitgebreider geworden (aantal left joins) maar SEC_TO_TIME doet het nu wel in mn PHP programmering, wanneer ik hem echter direct in PHPMyAdmin invoer doet hij het weer niet

Dus even de oplossing

SQL:

SELECT projecten.id,projecten.naam,werkgever.id,werkgever.achternaam,projecten.startdatum,projecten.einddatum,
   SEC_TO_TIME(
      SUM(    
          UNIX_TIMESTAMP(uren.end_date_time) - UNIX_TIMESTAMP(uren.start_date_time)    
      )
   )    
     AS tijd_gewerkt 
FROM werkgever, projecten
LEFT JOIN
    onderdeel ON
         (projecten.id = onderdeel.id_project)
LEFT JOIN
    uren ON
          (onderdeel.id = uren.id_onderdeel) 
WHERE  projecten.id_werkgever = werkgever.id 
GROUP BY projecten.id 

[LauPro]

frickY schreef op zaterdag 28 april 2007 @ 14:40:
Het is absoluut niet aan te raden data in seconden op te slaan. MySQL biedt je hele krachtige functies om met datetime's te werken, terwijl je bij timestamps van je eigen ffantasie en rekenvaardigheid afhankelijk bent.

Aan hele krachtige functies heb je niets als de grondslag ervan slecht is. Hoe ben jij er zeker van dat met een datetime veld de datum in de juiste tijdzone wordt opgeslagen (met zomer/wintertijd?). Het gebruik van DATE(TIME) is leuk voor simpele blogjes maar zodra het wat groter en serieuzer wordt dan ga je er keihard mee op je bek. Unix timestamps zijn gewoon the way to go. En er zijn ook een heleboel functies om fatsoenlijk met timestamps te rekenen. Bovendien werkt MySQL stiekum intern ook met timestamps dus het is eigenlijk alleen maar een jasje.

Ik denk dat zelf dat PHPMyAdmin je query niet snapt en hem dus corrupt maakt.

[Creepy]

Puur en alleen omdat jij denkt dat je timezones niet onder controle krijgt gaat iedereen hard onderuit als er gebruik wordt gemaakt van DateTime? Leg dat nog eens beter uit dan want ik vindt het een drogreden. Kleine moeite om alles GMT op te slaan. En die MySQL datum functies werken prima hoor

Puur en alleen omdat PHP handige timestamp functiees heeft maakt een timestamp niet automatisch een betere oplossing. Daarnaast heeft een timestamp ook nog eens bijwerkingen (de eerste timestamp in een kolom wordt automatisch bijgewerkt naar het huidige tijdstip als je de timestamp kolom niet meeneemt in een update).

Ook zijn er ook talen/omgevingen die een Date of DateTime type hebben waar ook nog eens perfect mee te rekenen valt die prima mapt op het DateTime type van MySQL.

[ Voor 6% gewijzigd door Creepy op 28-04-2007 20:25 ]

[LauPro]

Ik heb het niet over het type TIMESTAMP, maar over een unix timestamp in een integer field. Klinkt inderdaad ranzig, maar imo het enige tijdformaat dat per definitie GMT is (bij DATE(TIME) is het altijd maar raden zoals je zelf aan geeft) en je verlegd de presentatie ook meteen naar de applicatie.

Het is niet iets wat ik denk maar wat uit de praktijk blijkt. Zelfs het React forum gebruikt unix timestamps in een integer veld voor datum en tijd opslag. Dat is toch wel een stukje software dat buitengewoon schaalbaar en veel wordt ingezet. Ook zij willen zich niet branden aan geklooi met tijdzones.

Daarnaast vraag ik me af of het wel zo makkelijk is om vanuit je applicatie naar GMT te gaan. Heb je rekening gehouden met de zomertijd die je telkens moet verrekenen? Volgens mij komt het in de praktijk erop neer dat er helemaal geen rekening mee wordt gehouden en dat geeft bij de omschakeling tussen zomertijd naar wintertijd weer problemen (er lijkt 'opeens' een uur weg te zijn, sortering gaat raar doen).

[ Voor 3% gewijzigd door LauPro op 28-04-2007 23:44 ]

[Creepy]

Mij zal het een worst wezen wat React gebruikt en schaalbaarheid heeft naar mijn idee ook niks met deze discussie te maken.

Ik denk dat je nog steeds hetzelfde probleem hebt met een timestamp aangezien die ook vanaf een datum, in een bepaalde tijdzone al dan niet in daylight saving time geconverteerd moet worden. Of je nu wel of geen timestamp gebruikt maakt dat echt niet anders. Alle converies van een datum naar een timestamp (en terug) maken nog steeds gebruik van een timezone. En als je bijv. NOW() in een timestamp stopt of ermee gaat rekenen dan wordt ook dit berekend aan de hand van de opgegeven timezone van de machine waar de betreffende code op draait.

[RobIII]

LauPro schreef op zaterdag 28 april 2007 @ 23:43:
...en je verlegd de presentatie ook meteen naar de applicatie.

Misschien lees ik het verkeerd, maar mijn eerste reactie is toch écht: Daar hoort de verantwoordelijkheid van de presentatie ook, niet in de DB.

LauPro schreef op zaterdag 28 april 2007 @ 23:43:
Daarnaast vraag ik me af of het wel zo makkelijk is om vanuit je applicatie naar GMT te gaan. Heb je rekening gehouden met de zomertijd die je telkens moet verrekenen?

Als je de datetimefuncties (goed) toepast is dat waarschijnlijk beter in je applicatie te doen dan in je DB (buiten het feit dat de DB zich dus zowieso niet met presentatie bezig hoor te houden).

[LauPro]

Creepy schreef op zondag 29 april 2007 @ 01:03:
Mij zal het een worst wezen wat React gebruikt en schaalbaarheid heeft naar mijn idee ook niks met deze discussie te maken.

Prima, negeer de praktijk maar en ga op in de theoretische software. Nogmaals, wat ik al aan gaf, daarmee ga op een gegeven moment op je plaat.

Ik denk dat je nog steeds hetzelfde probleem hebt met een timestamp aangezien die ook vanaf een datum, in een bepaalde tijdzone al dan niet in daylight saving time geconverteerd moet worden. Of je nu wel of geen timestamp gebruikt maakt dat echt niet anders. Alle converies van een datum naar een timestamp (en terug) maken nog steeds gebruik van een timezone. En als je bijv. NOW() in een timestamp stopt of ermee gaat rekenen dan wordt ook dit berekend aan de hand van de opgegeven timezone van de machine waar de betreffende code op draait.

Dat is niet correct. Een unix timestamp is altijd GMT en zonder verrekening van zomertijd. Die zomertijd wordt er pas bij gerekend wanneer die van toepassing is. Dit verschilt namelijk per land/regio en per jaar. Dus wat je ook doet, zodra je een tijd om zet naar een unix timestamp dan is die ten alle tijden valide en om te rekenen naar de juiste tijd in de juiste tijdzone. Er zitten namelijk geen tijdzonegegevens in opgeslagen, dat gebeurt pas in je applicatie. NOW() is wat dat betreft niets anders dan de systeem unix timestamp.

Overigens zijn er wel systemen die intern hun unix timestamp opslaan in de huidige tijdzone om compatible the zijn met Windows (die wel de tijd van de huidige tijdzone op slaat) maar in principe is het in de *NIX en *BSD wereld om de interne klok op GMT te draaien.

Zie bijv. http://gatekeeper.dec.com.../src/lib/libc/time/Theory

[Creepy]

LauPro schreef op zondag 29 april 2007 @ 13:21:
[...]
Prima, negeer de praktijk maar en ga op in de theoretische software. Nogmaals, wat ik al aan gaf, daarmee ga op een gegeven moment op je plaat.

???? Hou hier maar over op, je hebt geen idee wat ik precies doe en op welke manier. Ik wilde alleen maar aangeven dat ik vind dat je met eigen argumenten moet komen i.p.v. te wijzen naar react. Jij weet namelijk niet de redening achter het gebruik van een timestamp daar (ik ook niet) maar je doet alsof je dat wel weet.

Dat is niet correct. Een unix timestamp is altijd GMT en zonder verrekening van zomertijd. Die zomertijd wordt er pas bij gerekend wanneer die van toepassing is. Dit verschilt namelijk per land/regio en per jaar. Dus wat je ook doet, zodra je een tijd om zet naar een unix timestamp dan is die ten alle tijden valide en om te rekenen naar de juiste tijd in de juiste tijdzone. Er zitten namelijk geen tijdzonegegevens in opgeslagen, dat gebeurt pas in je applicatie. NOW() is wat dat betreft niets anders dan de systeem unix timestamp.

En in een datetime zitten ook geen timezone gegevens opgeslagen. Hier ontgaat mij weer het voordeel van een timestamp. En zoals we allebei aangeven moet je dus wel degelijk geen rekenen met timezone's e.d om te kunnen converteren van en naar een datum die aan bijv. de gebruiker wordt getoond.

Overigens zijn er wel systemen die intern hun unix timestamp opslaan in de huidige tijdzone om compatible the zijn met Windows (die wel de tijd van de huidige tijdzone op slaat) maar in principe is het in de *NIX en *BSD wereld om de interne klok op GMT te draaien.

Zie bijv. http://gatekeeper.dec.com.../src/lib/libc/time/Theory

Het zal wel weer aan mij liggen maar ik zie niet in wat dit met het verschil tussen een datetime en een timestamp in MySQL te maken heeft.

Kan je met eigen argumenten nu eens aangeven waarom je altijd een timestamp gebruikt? Want ik zie op het moment er geen echte voordelen aanzitten. Daarnaast weet ik niet of je een timestamp ook aanraad als je alleen een datum wilt opslaan. Dat is wat mij betreft niet nuttig aangezien je nu ook tijdgevens opslaan terwijl je deze helemaal niet nodig hebt.

[ Voor 8% gewijzigd door Creepy op 29-04-2007 13:58 ]

[LauPro]

Creepy schreef op zondag 29 april 2007 @ 13:49:
???? Hou hier maar over op, je hebt geen idee wat ik precies doe en op welke manier. Ik wilde alleen maar aangeven dat ik vind dat je met eigen argumenten moet komen i.p.v. te wijzen naar react. Jij weet namelijk niet de redening achter het gebruik van een timestamp daar (ik ook niet) maar je doet alsof je dat wel weet.

Dit is slechts een voorbeeld er zijn legio applicaties die voor data/tijd opslag een unix timestamp gebruiken en ik noem toevallig even React als (imo) toonaangevend voorbeeld.

En in een datetime zitten ook geen timezone gegevens opgeslagen. Hier ontgaat mij weer het voordeel van een timestamp. En zoals we allebei aangeven moet je dus wel degelijk geen rekenen met timezone's e.d om te kunnen converteren van en naar een datum die aan bijv. de gebruiker wordt getoond.

Die zitten er wel in opgeslagen. Want wanneer je in een database 14:18:00 29-04-2007 op slaat (zegmaar nu) dan is dat de tijd volgens de huidige tijdzone. Voor het rekenen van een timestamp naar een presenteerbare tijd zijn prima functies aanwezig, dat hoef je niet zelf te doen, je hoeft slechts de formatting aan te geven.

Het zal wel weer aan mij liggen maar ik zie niet in wat dit met het verschil tussen een datetime en een timestamp in MySQL te maken heeft.
[..]
Kan je met eigen argumenten nu eens aangeven waarom je altijd een timestamp gebruikt? Want ik zie op het moment er geen echte voordelen aanzitten. Daarnaast weet ik niet of je een timestamp ook aanraad als je alleen een datum wilt opslaan. Dat is wat mij betreft niet nuttig aangezien je nu ook tijdgevens opslaan terwijl je deze helemaal niet nodig hebt.

Ik zeg helemaal niet dat ik altijd een timestamp gebruik. Maar in de gevallen dat er gerekend mee moet worden wel.

Stel het datamodel dat ik suggereerde in mijn eerdere posts ga ik gebruiken voor een urenregistratieprogramma. Dan zul je bij de omschakeling van de zomertijd naar wintertijd een uur mis lopen en andersom een uur teveel declareren. Je zou kunnen stellen dat het per saldo over het hele jaar niets uit maakt en dat het uitzonderlijk is. Maar ik heb persoon zo'n bug niet in mijn programma. Ik ben van mening dat als je een moment wilt registreren (dus instarten van iets) dat je dan per definitie een unix timestamp nodig hebt. Het is dan jammer dat MySQL geen formaat heeft waarin je een native unix timestamp kan op slaan (los van de INT).

Je zou een unix timestamp kunnen converteren naar een GMT-compatible weergave van het DATETIME-type en dan als je er iets mee wil gaan doen weer terugconverteren naar een unix timestamp en dan weer presenteren als een valide datum. Persoonlijk vind ik dat teveel gereken en je hebt er geen voordeel uit ook. De DATE(TIME) veldtypen zijn bedoeld dat je presentabel datum en tijdnotaties kan op slaan, als die vervolgens GMT zijn heb je er niets aan.

DATE(TIME) zou pas nuttig zijn als je ook kon aangeven in welke zone het zich af speelt. Maar ik vraag me af of dat een wenselijke ontwikkeling is omdat zonegegevens (zie de eerdere link) wisselen per jaar. In Nederland is de zonetijd alleen actief geweest in 1916 tot 1945 en vanaf 1977 tot nu en dit verschilt ook weer per zone/per regio. Aangezien je in een database eigenlijk alleen gegevens wilt hebben die altijd consistent zijn zie ik een unix timestamp eigenlijk alleen maar als het beste middel om dit te realiseren. Bedoel als je vandaag in een integer field de waarde 12 stopt, dan wil je toch niet dat die over een paar maanden 13 is? Dat is hetzelfde wat er met tijd wel kan gebeuren namelijk.

[Creepy]

LauPro schreef op zondag 29 april 2007 @ 14:18:
[...]
Dit is slechts een voorbeeld er zijn legio applicaties die voor data/tijd opslag een unix timestamp gebruiken en ik noem toevallig even React als (imo) toonaangevend voorbeeld.
[...]
Die zitten er wel in opgeslagen. Want wanneer je in een database 14:18:00 29-04-2007 op slaat (zegmaar nu) dan is dat de tijd volgens de huidige tijdzone. Voor het rekenen van een timestamp naar een presenteerbare tijd zijn prima functies aanwezig, dat hoef je niet zelf te doen, je hoeft slechts de formatting aan te geven.

Er zit alleen een datum en tijd opgeslagen in een DateTime, zonder enige timezone informatie. Pas als je er wat mee gaat doen kan het zijn dat je een timezone daarvoor nodig hebt.

Ik zeg helemaal niet dat ik altijd een timestamp gebruik. Maar in de gevallen dat er gerekend mee moet worden wel.

En dat ben ik dus niet altijd met je eens aangezien MySQL prima functies heeft om met DateTime velden e.d. goed te kunnen rekenen voor selecties e.d.

Stel het datamodel dat ik suggereerde in mijn eerdere posts ga ik gebruiken voor een urenregistratieprogramma. Dan zul je bij de omschakeling van de zomertijd naar wintertijd een uur mis lopen en andersom een uur teveel declareren. Je zou kunnen stellen dat het per saldo over het hele jaar niets uit maakt en dat het uitzonderlijk is. Maar ik heb persoon zo'n bug niet in mijn programma. Ik ben van mening dat als je een moment wilt registreren (dus instarten van iets) dat je dan per definitie een unix timestamp nodig hebt. Het is dan jammer dat MySQL geen formaat heeft waarin je een native unix timestamp kan op slaan (los van de INT).

Ik zie niet in hoe je uren te weinig of teveel declareerd. Op het moment dat je er mee gaat rekenen zul je altijd rekening moeten houden met zomer en wintertijd als dat in je timezone van toepassing is. Als je zonder deze gegevens gaat rekenen dan krijg je juist fouten doordat je dan "vergeet" een uur over te slaan of "vergeet" een uur extra te rekenen.

Je zou een unix timestamp kunnen converteren naar een GMT-compatible weergave van het DATETIME-type en dan als je er iets mee wil gaan doen weer terugconverteren naar een unix timestamp en dan weer presenteren als een valide datum. Persoonlijk vind ik dat teveel gereken en je hebt er geen voordeel uit ook. De DATE(TIME) veldtypen zijn bedoeld dat je presentabel datum en tijdnotaties kan op slaan, als die vervolgens GMT zijn heb je er niets aan.

Dat niet alleen, MySQL biedt een hoop extra functies voor berekeningen en selecties met een DateTime. Dat biedt voor mij voordelen die voor mij niet opwegen tegen het ene nadeel dat je noemt: het werken met timezones.

DATE(TIME) zou pas nuttig zijn als je ook kon aangeven in welke zone het zich af speelt. Maar ik vraag me af of dat een wenselijke ontwikkeling is omdat zonegegevens (zie de eerdere link) wisselen per jaar. In Nederland is de zonetijd alleen actief geweest in 1916 tot 1945 en vanaf 1977 tot nu en dit verschilt ook weer per zone/per regio. Aangezien je in een database eigenlijk alleen gegevens wilt hebben die altijd consistent zijn zie ik een unix timestamp eigenlijk alleen maar als het beste middel om dit te realiseren. Bedoel als je vandaag in een integer field de waarde 12 stopt, dan wil je toch niet dat die over een paar maanden 13 is? Dat is hetzelfde wat er met tijd wel kan gebeuren namelijk.

Normaal gesproken gaan berekenen van en naar een timestamp aan de hand van de timezone waar de MySQL server (of als je het vanuit je code doet, de server waar je code op draait). Als je "30-04-207 12:37" opslaat in (My)SQL zal daar altijd ongeacht de timezone altijd weer "30-04-207 12:37" uitkomen. Onafhankelijk in welke timezone je dit opvraag juist omdat er geen timezone informatie in zit opgeslagen. Wat je er in stopt, haal je er dus ook altijd weer uit. De DB gaat echt niet zomaar andere data berekenen als je van timezone wisselt ofzo.

[LauPro]

Creepy schreef op maandag 30 april 2007 @ 12:40:
Er zit alleen een datum en tijd opgeslagen in een DateTime, zonder enige timezone informatie. Pas als je er wat mee gaat doen kan het zijn dat je een timezone daarvoor nodig hebt.

In die datum en tijd die je op slaat zit al een tijdzone verwerkt, omdat dit veelal niet GMT is. En ik zie nog steeds niet in hoe vanuit een applicatie een datum die een user in voert 'makkelijk' kan omzetten naar een GMT-DATE(TIME) formaat. Overigens merk ik op dat je het over een DateTime hebt, impliceer je hiermee dat je een platform hebt die dit voor je regelt?

En dat ben ik dus niet altijd met je eens aangezien MySQL prima functies heeft om met DateTime velden e.d. goed te kunnen rekenen voor selecties e.d.

Natuurlijk kan er best gerekend worden met DATE(TIME), zie ook dit topic maar er ligt een enorme bug op de loer:

PHP:

$iDay = mktime(0,00,0,10,28,2007); // laatste zondag van oktober
for($i = 0; $i <= 100; $i++) {
    echo $iDay.' '.date('r',$iDay).'<br />';
    $iDay+=1800;
}

Genereert:

code:

1193522400 Sun, 28 Oct 2007 00:00:00 +0200
1193524200 Sun, 28 Oct 2007 00:30:00 +0200
1193526000 Sun, 28 Oct 2007 01:00:00 +0200
1193527800 Sun, 28 Oct 2007 01:30:00 +0200
1193529600 Sun, 28 Oct 2007 02:00:00 +0200
1193531400 Sun, 28 Oct 2007 02:30:00 +0200
1193533200 Sun, 28 Oct 2007 02:00:00 +0100 <-- Omschakeling wintertijd
1193535000 Sun, 28 Oct 2007 02:30:00 +0100
1193536800 Sun, 28 Oct 2007 03:00:00 +0100
1193538600 Sun, 28 Oct 2007 03:30:00 +0100
1193540400 Sun, 28 Oct 2007 04:00:00 +0100
1193542200 Sun, 28 Oct 2007 04:30:00 +0100

Daar zie je dus dat het volgens onze tijd een uur langer '2 uur' blijft. Terwijl je dit uur wel gewoon door leeft. Als je dus DATETIME gebruikt 'vervalt' dit uur terwijl de unix timestamp wel gewoon door loopt. Beter bewijs dan dit kan ik denk ik niet leveren. Stel dat de TS in de nacht van 28 october werkt aan een storing, en hij begint om 1 uur en is om 3 uur klaar, heeft hij dan 2 of 3 uur gewerkt? Als je het op slaat in een DATETIME zul je het nooit weten, maar met een GMT unix timestamp weet je het wel.

Ik zie niet in hoe je uren te weinig of teveel declareerd.

Zie bovenstaand voorbeeld dus.

Normaal gesproken gaan berekenen van en naar een timestamp aan de hand van de timezone waar de MySQL server (of als je het vanuit je code doet, de server waar je code op draait). Als je "30-04-207 12:37" opslaat in (My)SQL zal daar altijd ongeacht de timezone altijd weer "30-04-207 12:37" uitkomen. Onafhankelijk in welke timezone je dit opvraag juist omdat er geen timezone informatie in zit opgeslagen. Wat je er in stopt, haal je er dus ook altijd weer uit. De DB gaat echt niet zomaar andere data berekenen als je van timezone wisselt ofzo.

Ik ga er vanuit dat een unix timestamp altijd GMT is. Dit is wel gebruikelijk, er zijn systemen die dit niet doen, maar dan verrekent de MySQL server dit naar GMT intern (of eigenlijk de libs waar MySQL gebruik van maakt).

Hoop dat je nu meer overtuigd bent.

[ Voor 5% gewijzigd door LauPro op 30-04-2007 15:34 ]

[frickY]

Selecteer je voor mij even alle records opgeslagen in week 23 tussen 9:00 en 18:00 in de afgelopen 25 jaar. Mag jij het in timestamps doen, doe ik het met een datetime-kolom;

SQL:

SELECT * FROM tabel WHERE datum >= DATE_SUB(NOW(), INTERVAL 25 YEAR) AND WEEK(datum) = 23 AND HOUR(datum) >= 9 AND HOUR(datum) <= 18

Voor het opslaan van een tijdstip welke je alleen ter referentie gebruikt maakt het weinig uit of je het als timestamp of als datetime doet. Maar zodra je ermee moet rekenen of vergelijken lijkt een datetime me voor de hand liggender.
Je tijdzone voorbeeld vind ik overigens een beetje oneerlijk. Je hoeft niet in onze tijdzone te werken met datetimes, je mag ook gewoon de GMT pakken voor een eerlijke vergelijking.

[ Voor 6% gewijzigd door frickY op 30-04-2007 14:47 ]

[ACM]

LauPro schreef op maandag 30 april 2007 @ 14:25:
Hoop dat je nu meer overtuigd bent.

't Probleem zit hem eigenlijk niet in het gebruiken van datetime/timestamp maar mysql's gebrekkige implementatie ervan. Ze hebben o.a. geen timezone-ondersteuning erbij etc. Dus je moet alsnog gaan opslaan in UTC of de timezone apart opslaan. In een database die fatsoenlijke ondersteuning voor timezones (en voor date-datatypen in zijn algemeenheid) is er eigenlijk geen enkel bezwaar tegen, zoals dit voorbeeld met postgresql:

code:

log=# select * from uren ;
 id | onderdeel |         start          |          endt
----+-----------+------------------------+------------------------
  1 |         1 | 2007-04-28 13:50:19+02 | 2007-04-28 14:50:26+02
  2 |         1 | 2007-04-28 16:50:57+02 | 2007-04-28 18:51:03+02
  3 |         2 | 2007-04-28 13:54:20+02 | 2007-04-28 17:54:25+02
  4 |         3 | 2007-10-28 01:00:00+02 | 2007-10-28 05:00:00+01
(4 rows)

log=# select onderdeel, sum(endt - start) as tijd from uren
log-# group by onderdeel order by onderdeel;
 onderdeel |   tijd
-----------+----------
         1 | 03:00:13
         2 | 04:00:05
         3 | 05:00:00

Zoals je ziet is er aan onderdeel netjes 5 uur tijd genoteerd. Bovendien zie je dat postgres gewoon een - tussen twee timestamp's accepteert en de sum dus aanzienlijk eenvoudiger wordt... Postgres maakt uit er zichzelf een interval van, ipv een of andere gekke integer die iets met seconden voorstelt.

De topicstarter kan trouwens wel zonder die unix_timestamp's te gebruiken de boel uitrekenen, vziw maakt mysql er sowieso wel seconden van dan. Werking en problemen blijven hetzelfde, maar de query is dan iig weer wat leesbaarder.

[LauPro]

frickY schreef op maandag 30 april 2007 @ 14:45:
Voor het opslaan van een tijdstip welke je alleen ter referentie gebruikt maakt het weinig uit of je het als timestamp of als datetime doet. Maar zodra je ermee moet rekenen of vergelijken lijkt een datetime me voor de hand liggender.

Dit is dus precies wat ik eerder heb gesteld. Als het gaat om tijdstip waarmee wordt gerekend zul je altijd een unix timestamp nodig hebben in MySQL. En er zijn genoeg tools beschikbaar om ook de query die jij wil met unix timestamps te doen, ook in MySQL (vergelijkbaar met wat jij doet).

Je tijdzone voorbeeld vind ik overigens een beetje oneerlijk. Je hoeft niet in onze tijdzone te werken met datetimes, je mag ook gewoon de GMT pakken voor een eerlijke vergelijking.

Welk platform zorgt er dan voor dat de tijden die een user invoert een beetje makkelijk GMT worden in het MySQL DATETIME formaat? Wat ik al aan gaf komt hier veel meer bij kijken namelijk.

ACM schreef op maandag 30 april 2007 @ 15:12:
't Probleem zit hem eigenlijk niet in het gebruiken van datetime/timestamp maar mysql's gebrekkige implementatie ervan. Ze hebben o.a. geen timezone-ondersteuning erbij etc. Dus je moet alsnog gaan opslaan in UTC of de timezone apart opslaan. In een database die fatsoenlijke ondersteuning voor timezones (en voor date-datatypen in zijn algemeenheid) is er eigenlijk geen enkel bezwaar tegen, zoals dit voorbeeld met postgresql:

Dit is ook toch precies wat ik heb gesteld? Je kan in MySQL bij een DATETIME geen tijdzone aangeven en het veldtype is dus in mijn ogen daarmee vrijwel waardeloos. Meer kan ik er niet van maken. En iedereen die stelt dat een DATETIME wel prima kan worden gebruikt ook in combinatie met berekeningen die loopt dus kans om (in tijdzone regio Nederland) 2 keer per jaar 'genaaid' te worden .

[Creepy]

LauPro schreef op maandag 30 april 2007 @ 14:25:
[...]
In die datum en tijd die je op slaat zit al een tijdzone verwerkt, omdat dit veelal niet GMT is. En ik zie nog steeds niet in hoe vanuit een applicatie een datum die een user in voert 'makkelijk' kan omzetten naar een GMT-DATE(TIME) formaat. Overigens merk ik op dat je het over een DateTime hebt, impliceer je hiermee dat je een platform hebt die dit voor je regelt?
[...]

In MySQL heb je een type DateTime. In Delphi heb je idd oko een DateTime class en in bijv. Java een Date class.

Natuurlijk kan er best gerekend worden met DATE(TIME), zie ook dit topic maar er ligt een enorme bug op de loer:

PHP:

$iDay = mktime(0,00,0,10,28,2007); // laatste zondag van oktober
for($i = 0; $i <= 100; $i++) {
    echo $iDay.' '.date('r',$iDay).'<br />';
    $iDay+=1800;
}

Genereert: *snip*
Daar zie je dus dat het volgens onze tijd een uur langer '2 uur' blijft. Terwijl je dit uur wel gewoon door leeft. Als je dus DATETIME gebruikt 'vervalt' dit uur terwijl de unix timestamp wel gewoon door loopt. Beter bewijs dan dit kan ik denk ik niet leveren. Stel dat de TS in de nacht van 28 october werkt aan een storing, en hij begint om 1 uur en is om 3 uur klaar, heeft hij dan 2 of 3 uur gewerkt? Als je het op slaat in een DATETIME zul je het nooit weten, maar met een GMT unix timestamp weet je het wel.
[...]

Als je domweg het uren verschil gaat zitten rekenen dan verdwijnt er een uur ja. Als je rekent met de juiste timezone erbij dan is er niks aan de hand. In java mapt een DateTime uit bijv. MySQL prima op een object van het type Date. Ook een date heeft geen timezone informatie. Dus wat je in MySQL opslaat komt er 1 op 1 weer uit. Als je vervolgens gaat rekenen met timezone informatie of je rekent met GMT is er niks aan de hand en heb je geen fouten. Sterker nog, zelfs als ik in onze timezone hier gebruik rekent het zelfs prima.

code:

try {
            Date startDate = sdf.parse("28-10-2007 00:00");
            GregorianCalendar cal = new GregorianCalendar(TimeZone.getTimeZone("Europe/Amsterdam"));
            
            cal.setTime(startDate);
            cal.add(Calendar.HOUR,5);
            System.out.println(sdf.format(cal.getTime()));
            long diff = (cal.getTimeInMillis() - startDate.getTime()) / 1000 / 60 / 60;
            
            System.out.println("Diff = " + diff + " hours");
            
            
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

Uitkomst:
28-10-2007 04:00
Diff = 5 hours

En omdat een DateTime van MySQL (of welk ander DBMS dan ook) 1 op 1 mapt op een Date in Java is er niks aan de hand omdat het eenvoudig is om alle berekening in GMT te doen en zelfs als je dat niet doet er netjes rekening wordt gehouden met de zomer/wintertijd van de betreffende timezone. Hierbij maakt het voor mij niet uit of ik een unix timestamp opsla in een int of dat ik een DateTime gebruik.

[ Voor 8% gewijzigd door Creepy op 30-04-2007 18:19 ]

[ACM]

Creepy schreef op maandag 30 april 2007 @ 18:14:
En omdat een DateTime van MySQL (of welk ander DBMS dan ook) 1 op 1 mapt op een Date in Java is er niks aan de hand omdat het eenvoudig is om alle berekening in GMT te doen en zelfs als je dat niet doet er netjes rekening wordt gehouden met de zomer/wintertijd van de betreffende timezone.

Maar je kan het dus niet zomaar in queries gebruiken in MySQL, tenzij je nauturlijk alle datums als UTC opslaat. Het zal niet altijd verstandig zijn de berekeningen naar je applicatielaag te verhuizen terwijl je database het vaak een stuk efficienter kan.
En je geeft in je voorbeeld de omgekeerde versie, werkt het ook als je twee date's maakt, een van "28-10-2007 00:00" en een van "28-10-2007 04:00" en dan vraagt hoeveel tijd verschil er tussen zit?

MySQL kan ook dat niet en andere databases wel:

mysql> select '2007-10-28 00:00:00' + interval 5 hour;
+-----------------------------------------+
| '2007-10-28 00:00:00' + interval 5 hour |
+-----------------------------------------+
| 2007-10-28 05:00:00                     |
+-----------------------------------------+

select '2007-10-28 00:00:00'::timestamp with time zone + interval '5 hour';
        ?column?
------------------------
 2007-10-28 04:00:00+01

Kortom, het argument van Laupro blijft gewoon bestaan, je moet heel erg uitkijken met timestamps/datetimes in MySQL omdat ze geen rekening houden met timezone's en dus niet met winter- en zomertijd. Dat Java en andere programmeeromgevingen het vervolgens wel goed kunnen is mooi, maar niet altijd bruikbaar of handig te doen.

[Creepy]

Het argument van Laupro was naar mijn idee: altijd timestamps als een int opslaan als je gaat rekenen met data. De nadelen die Laupro noemt vind ik niet genoeg om altijd een timestamp als int op te slaan in MySQL i.p.v. een DateTime type te gebruiken. Daarnaast valt dit nadeel geheel weg als je alleen van een Date gebruik maakt of alles als UTC of GMT opslaat.

En ja, als je in mijn voorbeeld er geen 5 uur bij optelt maar een tweede Date instantieert met "28-10-2007 04:00" dan komt ook daar netjes 5 uur verschil uit.

[ Voor 11% gewijzigd door Creepy op 30-04-2007 20:12 ]

[Verwijderd]

conclusie: geen MySQL gebruiken

[LauPro]

Ik kijk er toch iets anders tegen aan. Al meer dan 30 jaar is er een hele solide standaard (POSIX) om datums en tijden op te slaan, namelijk in een 32-bit signed integer. Opeens heeft een of andere database fetisjist bedacht dat datums niet meer in die integer op te slaan maar in een meer presentabel formaat.

Persoonlijk ben ik van mening dat je op het LAMP-platform het beste gewoon een integer kan blijven gebruiken. Want met PHP is het gewoon heel lastig om anders datums op te slaan in een database. Of je moet zoals bij sommige Java database connectors die conversie in je databaselaag stoppen. Maar dan is het tijd voor een fatsoenlijke DBA voor php en die is er nog niet.

Dat is een beetje dezelfde discussie als met het opslaan van IP-adressen in een database. Menige zijn er van overtuigd dat hier een apart veld voor zou moeten komen, dit terwijl een IP-adres makkelijk is om te rekenen naar een 32-bit integer ook weer.

MySQL kan je prima gebruiken icm PHP, maar blijf dan wel binnen de kaders van het platform. En als het platform POSIX is, dan ontkom je bijna niet aan de integer timestamp .

[ACM]

LauPro schreef op maandag 07 mei 2007 @ 01:13:
Ik kijk er toch iets anders tegen aan. Al meer dan 30 jaar is er een hele solide standaard (POSIX) om datums en tijden op te slaan, namelijk in een 32-bit signed integer. Opeens heeft een of andere database fetisjist bedacht dat datums niet meer in die integer op te slaan maar in een meer presentabel formaat.

Dat is wel een heel rare uitspraak... Je weet toch wel dat de databases dat ding doorgaans niet 'presentabel' op slaan? De meesten zullen er bijvoorbeeld een 8byte veld voor gebruiken, zodat ze naast de eventuele time zone ook een hogere resolutie dan die secondes kwijt kunnen.
En dan even het allerbeste... die posix datum kan maar vanaf 1 januari 1970 (ga daar maar eens geboortedatums in opslaan) en eindigt ergens in 2030 (er zijn databases die dan nog steeds bruikbaar moeten zijn). Postgres' timestamp-formaat komt bijvoorbeeld met een resolutie van microseconden (14 digits) toch nog van 4713 BC t/m 5874897 AD... en dat in 8 bytes.

Dus ik betwijfel of er 'opeens' een of andere 'fetisjist' vond dat ie moest afwijken van die 'hele solide standaard'. Ik gok eerder dat er een aantal mensen die POSIX-opslagmethode veel te beperkt vonden en een eigen (pre-SQL al) vorm bedachten.

Persoonlijk ben ik van mening dat je op het LAMP-platform het beste gewoon een integer kan blijven gebruiken.

Je moet imho gewoon de beste oplossing voor je toepassing gebruiken. Als dat betekent dat je integers kan gebruiken omdat je toch alleen maar met de komende 4 jaar rekening hoeft te houden en enkel in seconden hoeft te rekenen, zonder achteraf eventuele afwijkende time zone's te weten, dan is dat waarschijnlijk wel het handigst icm php.

Dat is een beetje dezelfde discussie als met het opslaan van IP-adressen in een database. Menige zijn er van overtuigd dat hier een apart veld voor zou moeten komen, dit terwijl een IP-adres makkelijk is om te rekenen naar een 32-bit integer ook weer.

En ook daar geldt dat je de beste oplossing moet gebruiken. In PostgreSQL hebben ze bijv een 'inet' datatype (en ook een ipv4 oid, maar dat is een 'contrib') die zowel ipv4 als ipv6 op kan slaan en waar je triviaal mee kan rekenen, ala: '192.168.1.24' << '192.168.0.0/16' om te weten of een ip in een bepaald domein valt, zonder dat je gezeik hebt met het omrekenen van bereiken naar between-statements. Bovendien heb je in php nog eens dat de integer-range niet groot genoeg is voor ipadressen, waardoor je daar goed mee uit moet kijken met je ranges en opslag.

MySQL kan je prima gebruiken icm PHP, maar blijf dan wel binnen de kaders van het platform. En als het platform POSIX is, dan ontkom je bijna niet aan de integer timestamp .

Niet eens, gewoon de beste tool for the job gebruiken. Maar vaak zal integers het handigste blijken te zijn ja.

[LauPro]

ACM schreef op maandag 07 mei 2007 @ 07:45:
Dat is wel een heel rare uitspraak... Je weet toch wel dat de databases dat ding doorgaans niet 'presentabel' op slaan? De meesten zullen er bijvoorbeeld een 8byte veld voor gebruiken, zodat ze naast de eventuele time zone ook een hogere resolutie dan die secondes kwijt kunnen.
En dan even het allerbeste... die posix datum kan maar vanaf 1 januari 1970 (ga daar maar eens geboortedatums in opslaan) en eindigt ergens in 2030 (er zijn databases die dan nog steeds bruikbaar moeten zijn). Postgres' timestamp-formaat komt bijvoorbeeld met een resolutie van microseconden (14 digits) toch nog van 4713 BC t/m 5874897 AD... en dat in 8 bytes.

Wel, mijn nadruk op de 32bit is niet helemaal juist. Want tegenwoordig zijn de meeste databases 64bit (jouw 8 bytes) waarbij je uiteindelijk ook een dergelijke typestamp in op zou moeten slaan. De reden dat de 64bit unix timestamp nog niet echt is ingeburgerd (al hoewel een modern 64bit Linux systeem er wel mee zal rekenen) is omdat het de binary compatiblity verbreekt. Toch zijn er een aantal libs die al de 64bit timestamp ondersteunen. In ieder geval, ik verwacht wel dat platforms die nu in gebruik zijn en die na 2038 nog moeten werken voor die tijd zijn gemoderniseerd naar native 64bit.

Dus ik betwijfel of er 'opeens' een of andere 'fetisjist' vond dat ie moest afwijken van die 'hele solide standaard'. Ik gok eerder dat er een aantal mensen die POSIX-opslagmethode veel te beperkt vonden en een eigen (pre-SQL al) vorm bedachten.

We zijn er over eens dat de DATETIME in MySQL niet echt praktisch is en persoonlijk komt het op mij over dat die er nog even snel bijgeklust is 'omdat een database dat moet hebben'.

En ook daar geldt dat je de beste oplossing moet gebruiken. In PostgreSQL hebben ze bijv een 'inet' datatype (en ook een ipv4 oid, maar dat is een 'contrib') die zowel ipv4 als ipv6 op kan slaan en waar je triviaal mee kan rekenen, ala: '192.168.1.24' << '192.168.0.0/16' om te weten of een ip in een bepaald domein valt, zonder dat je gezeik hebt met het omrekenen van bereiken naar between-statements. Bovendien heb je in php nog eens dat de integer-range niet groot genoeg is voor ipadressen, waardoor je daar goed mee uit moet kijken met je ranges en opslag.

Rekenen met IP-adressen in in essentie rekenen met integers. En met een aantal hele simpele basis operatoren kan je ook zonder fancy inet-type rekenen met IP-adressen. Neemt niet weg dat 'databasetechnisch' het inderdaad mooier is om er een aparte type voor te hebben. Persoonlijk heb ik het toch niet zo op dit soort custom implementaties, het doet wel meteen de deur dicht naar andere DBMS'en.