[SQL] Van timestamp naar datum rekenen en opslaan in tabel

[Verwijderd]

Ik heb een tabel (messages) met daarin een timestamp (created_at) waarop elk bericht is aangemaakt:

code:

message_id timestamp  text
-------------------------------------
1          1298063318 Hier een beicht
2          1298053197 Nogiets leuks

De tabel bevat een paar miljoen records. Nu wil ik graag statistieken hieruit halen, bijvoorbeeld door alle berichten van een bepaalde datum te tellen. Hiervoor gebruik ik o.a. de volgende SQL:

SQL:

SELECT DATE_FORMAT(FROM_UNIXTIME(messages.created_at), '%Y-%m-%d') AS theDate, ...

Probleem: het bij elke recordset berekenen van de (human-readable) datum zorgt ervoor dat elke query enorm lang duurt (en dat er zelfs een timeout plaatsvindt). Oplossing: een extra kolom aanmaken (date_rendered) waarin voor elke message de timestamp al is omgerekend:

code:

message_id date_rendered timestamp  text
---------------------------------------------------
1          2011-02-01    1298063318 Hier een beicht
2          2011-02-02    1298053197 Nogiets leuks

Nu kan ik dit zelf vrij eenvoudig gedaan krijgen mbv een php scriptje, maar ik ben bang dat dit veel langer duurt dan noodzakelijk. Vandaar mijn vraag: als ik de date_rendered kolom nu aanmaak, is er dan een query waarmee ik (dus binnen MySQL), de yyyy-mm-dd datum van elke timestamp kan uitrekenen en kan toevoegen aan de date_rendered kolom?

[Matis]

Waarom formatteer je je timestamp niet in de presentatie-laag ipv de datalaag?

[NMe]

Matis schreef op zondag 27 november 2011 @ 16:15:
Waarom formatteer je je timestamp niet in de presentatie-laag ipv de datalaag?

^^ dat dus. Je zit een probleem op te lossen dat je niet zou hebben als je de presentatie dáár doet waar hij hoort en dat database laat doen waar hij goed in is: data opslaan.

[Ventieldopje]

De tabel bevat een paar miljoen records. Nu wil ik graag statistieken hieruit halen, bijvoorbeeld door alle berichten van een bepaalde datum te tellen.

Kun je dan niet gewoon een COUNT(created_at) doen met een WHERE voorwaarde zoals WHERE created_at = 'jetimestamp';

Dan ben je er toch ook?

[Matis]

Ventieldopje schreef op zondag 27 november 2011 @ 16:18:
[...]


Kun je dan niet gewoon een COUNT(created_at) doen met een WHERE voorwaarde zoals WHERE created_at = 'jetimestamp';

Dan ben je er toch ook?

Inderdaad, een eenvoudige kleiner/gotere/between clausule, en je bent er.

[CyBeR]

Ventieldopje schreef op zondag 27 november 2011 @ 16:18:
[...]


Kun je dan niet gewoon een COUNT(created_at) doen met een WHERE voorwaarde zoals WHERE created_at = 'jetimestamp';

Nee, want dan moet 'ie created_at dat exacte moment zijn.

Hij moet voor elke datum die 'ie wil nalopen even uitzoeken wat daar het begin en eind van is, en dan selecteren op timestamps die tussen die waarden liggen.

Je begin- en einddatum vind je natuurlijk weer aan de hand van de laagste en hoogste timestamps.

En ik meen dat MySQL wat functies heeft die selecteren op datum simpeler maken.

http://dev.mysql.com/doc/...ors.html#operator_between

[ Voor 12% gewijzigd door CyBeR op 27-11-2011 16:23 ]

[Verwijderd]

Het (key)woord wat je zoekt is between.

[Verwijderd]

NMe schreef op zondag 27 november 2011 @ 16:17:
[...]

^^ dat dus. Je zit een probleem op te lossen dat je niet zou hebben als je de presentatie dáár doet waar hij hoort en dat database laat doen waar hij goed in is: data opslaan.

Volgens mij kan het niet anders. Iets meer context:

code:

messages:
------------------------------
message_id category created_at
------------------------------
1          1        1298063318
2          1        1298053197

stocks:
-----------------------------------
category date        close volume
-----------------------------------
1        1313013600  12.25 40370600
1        1312927200  11.60 37281300

wiki:
-------------------------
category date       views
-------------------------
1        1296514800   550
1        1296601200   504

Ik wil nu (1) overzicht van alle unieke dagen waarop er messages zijn gepubliceerd van elke categorie (2) voor elk van die unieke dagen de close, volume en views waarden, aldus:

code:

---------------------------------------
date        messages close volume views
---------------------------------------
2011-02-13  4533     12.25 40370600 550
2011-02-14  6534     11.60 53543564 340
2011-02-16  5333     13.10 56464333 664

Als er op een bepaalde datum geen messages zijn, dan hoeven ook de andere data niet verzameld te worden voor die dag. In dit voorbeeld waren er geen messages voor 2011-02-15. Hoewel er mss wel close, volume en views waarden in de stocks en wiki tabellen stonden, zijn die genegeerd.

De query die ik voor dit overzicht wil gebruiken:

SQL:

SELECT 
  DATE_FORMAT(FROM_UNIXTIME(m.created_at), '%Y-%m-%d') AS date, 
  COUNT(m.message_id) AS messages,
  s.close,
  s.volume,
  w.views
FROM messages m
  LEFT JOIN stocks s ON m.category = s.category
       and FROM_UNIXTIME(m.created_at,'%Y-%m-%d')=FROM_UNIXTIME(s.date,'%Y-%m-%d')
  LEFT JOIN wiki w ON m.category = w.category
       and FROM_UNIXTIME(m.created_at,'%Y-%m-%d')=FROM_UNIXTIME(w.date,'%Y-%m-%d')
WHERE m.category = 1
GROUP BY date, s.close, s.volume, w.views
ORDER BY date ASC

Dit is te zwaar voor mijn laptop (homeservertje). Vandaar het voorstel voor de extra kolom...

[The Eagle]

Ik zie net je code. Waarom gebruik je in de joins al functies om de DTTM stamps aan te passen, terwijl je die eigenlijk ook rechtstreeks kunt joinen
Volgens mij zit daar je performance issue

Verwijderd schreef op zondag 27 november 2011 @ 16:22:
Het (key)woord wat je zoekt is between.

Vergis je niet in de between operator. Ik heb al verschillende DBMS'en gezien (waaronder zelfs jongens als Oracle) waarbij de between operator niet 100% correct geimplementeerd was. Daarbij: "between" betekent tussen, maar welke waarden tel je wel en welke tel je niet meer mee.
Oftewel:
Vraag je eerst af of je
1) A < X < B
2) A <= X <= B
3) A < X <= B
4) A<= X < B
moet hebben.
Wil je zeker weten dat je het goed doet, dan is mijn advies om nog steeds de logische operatoren te gebruiken ipv de between functie

[ Voor 12% gewijzigd door The Eagle op 27-11-2011 16:35 ]

[NMe]

Verwijderd schreef op zondag 27 november 2011 @ 16:22:
Het (key)woord wat je zoekt is between.

Between is niet meer dan een kortere manier van schrijven voor x < a and y > a. Nog naast het feit dat je dan (zeker bij timestamps) op moet passen dat je geen secondes meetelt waar je ze niet mee wil tellen, enz.

[Remus]

NMe schreef op zondag 27 november 2011 @ 17:03:
[...]

Between is niet meer dan een kortere manier van schrijven voor x < a and y > a. Nog naast het feit dat je dan (zeker bij timestamps) op moet passen dat je geen secondes meetelt waar je ze niet mee wil tellen, enz.

BETWEEN is inclusive op de boundaries dus >= en <=

6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".

Uit: ISO/IEC 9075:1992 (SQL 92), section 8.3 <between predicate>

[cariolive23]

timestamp? En dan ook nog een Unix-timestamp? Wat is er mis met de datatypes DATE en DATETIME?

Dat maakt de code ook een heel stuk testbaarder, geen (normaal) mens die in één keer kan zien dat 1298063318 ergens in 2011 viel, laat staan hoe laat het dan was. Het lost jouw probleem ook op, zowel met een DATE als een DATETIME (die je kunt casten naar een DATE) kun je snel tellen hoeveel records er per dag zijn aangemaakt.

[Verwijderd]

cariolive23 schreef op zondag 27 november 2011 @ 17:55:
timestamp? En dan ook nog een Unix-timestamp? Wat is er mis met de datatypes DATE en DATETIME?

Dat maakt de code ook een heel stuk testbaarder, geen (normaal) mens die in één keer kan zien dat 1298063318 ergens in 2011 viel, laat staan hoe laat het dan was. Het lost jouw probleem ook op, zowel met een DATE als een DATETIME (die je kunt casten naar een DATE) kun je snel tellen hoeveel records er per dag zijn aangemaakt.

Vandaar ook mijn vraag uit de TS. Misschien kan iemand daar wat meer van zeggen?

Verwijderd schreef op zondag 27 november 2011 @ 16:14:
is er dan een query waarmee ik (dus binnen MySQL), de yyyy-mm-dd datum van elke timestamp kan uitrekenen en kan toevoegen aan de date_rendered kolom?

[Voutloos]

Ja tuurlijk kan dat. Dat is enkel een UPDATE [...] SET nieuweDatumKolom = DATE_FORMAT('rapapa');

Maar je wil wellicht nog eerst over alle datatypes goed nadenken, en inderdaad bedenken of die joins niet anders moeten.

[cariolive23]

Verwijderd schreef op zondag 27 november 2011 @ 18:01:
[...]

Vandaar ook mijn vraag uit de TS. Misschien kan iemand daar wat meer van zeggen?

[...]

De boel omzetten naar een normaal datatype, eentje waar je wel iets mee kunt en waar je geen geknutsel voor nodig hebt. Vergeet het idee van een unix-timestamp, dat is ooit bedacht omdat er in die tijd nog een totaal gebrek aan resources was. Anno 2011, 40 jaar later dus, heb je daar echt geen last meer van.

Ga de boel dus eerst omzetten naar het juiste formaat, backup maken, kolom toevoegen, data omzetten, testen, foute kolom weggooien, code aanpassen. En dan ga je verder met het probleem dat je in de TS aankaart. Dat probleem is dan grotendeels ook al opgelost.

[ReenL]

Denormaliseren kan soms handig zijn voor performance, bij een group_by vind ik een datum kolom hebben helemaal niet zo gek eigenlijk. Zelf vermeid ik liefst timestamps omdat je met date(times) makkelijker kan rekenen / selecteren.

Anyway:
Alter table om de kolom toe te voegen en vervolgens de volgende query:
UPDATE table SET date_col = UNIX_FORMAT(timestamp_col, '%Y-%m-%d');

Zoals je ziet kun je in een update gewoon kolommen selecteren die in de huidige row zitten en er bewerkingen op uitvoeren. Zo ook bijvoorbeeld:
UPDATE table SET field = field + 1;

Welke raise condition save is.

[edit] Sorry voutloos dat ik je herhaal, had over je post heen gekeken...

[ Voor 6% gewijzigd door ReenL op 27-11-2011 20:34 ]

[GlowMouse]

Je geeft aan dat uitrekenen in MySQL te lang duurt. Dat is onzin. Het probleem zit allereerst in je JOINS.

SQL:

SELECT 
  DATE_FORMAT(FROM_UNIXTIME(m.created_at), '%Y-%m-%d') AS date, 
  COUNT(m.message_id) AS messages,
  s.close,
  s.volume,
  w.views
FROM messages m
  LEFT JOIN stocks s ON m.category = s.category
       and FROM_UNIXTIME(m.created_at,'%Y-%m-%d')=FROM_UNIXTIME(s.date,'%Y-%m-%d')
  LEFT JOIN wiki w ON m.category = w.category
       and FROM_UNIXTIME(m.created_at,'%Y-%m-%d')=FROM_UNIXTIME(w.date,'%Y-%m-%d')
WHERE m.category = 1
GROUP BY date, s.close, s.volume, w.views
ORDER BY date ASC

check die joins, die kunnen nu nooit met een index worden uitgevoerd. Denormaliseren in s en w en een index in s en w op de gedenormaliseerde kolommen gaat deze query veel sneller maken. Wil je verder, kun je ook in m denormaliseren en daarbij w.views uit de GROUP BY halen en in m een index maken op (date,close,volume), dan wordt 'ie echt snel.

[ Voor 3% gewijzigd door GlowMouse op 28-11-2011 00:13 ]