[PHP/MySQL] Statistieken uit database halen (datum en tijd)

[Reveller]

Ik heb een aantal statistieken in een tabel opgeslagen met o.a. de volgende kolommen en records:

code:

+------------+------+----------+
|date        | hits | sessions |
+------------+------+----------+
| 2007-04-18 |  47  |        4 |
+------------+------+----------+
| 2007-04-17 | 100  |        5 |
+------------+------+----------+
| 2007-04-15 |  64  |        4 |
+------------+------+----------+
| 2007-04-14 |  43  |        3 |
+------------+------+----------+
| 2007-04-13 |  54  |        5 |
+------------+------+----------+
| 2007-04-12 |  76  |        2 |
+------------+------+----------+

Nu wil een overzicht geven van het aantal hits en sessies vandaag, gisteren, deze week (tot nu toe), vorige week, 2 weken geleden. De eerste lukte me na wat denkwerk:

vandaag:

code:

db_query('SELECT COUNT(hits) FROM stats_dt WHERE YEAR(date) = %d AND 
MONTH(date) = %d AND DAYOFMONTH(date) = %d', date('Y'), date('m'),
date('d'))

Daarna loop ik echter vast:

• ik weet niet hoe ik moet vertellen wat de datum van gisteren is. Ik zou date('d') - 1 kunnen doen, maar dat werkt niet als het vandaag 1 mei is, en gisteren 30 april
• als de week op zondag begint, dan kan ik vanaf vandaag terugtellen: vandaag is het de 18e, dus dinsdag was het de 17e, maandag de 16e en zondag de 15e. Als ik dus de stats wil hebben van deze week (tot nu toe), tel ik die van de 15e t/m de 18e bij elkaar op. Maar ook hier geldt: hoe doe ik dat in mijn code? Met behulp van mktime in PHP of kan het direct mbv SQL?

De problemen voor het samenstellen van een query voor de hits van 2 weken geleden, afgelopen maand, vorige maand en afgelopen jaar zijn hetzelfde. Graag tips

[Alain]

Is het niet handiger om timestamps te gebruiken?

En dan vergelijken met de timestamps van de dag daarvoor, en daarvoor enz.

[Reveller]

AlainS schreef op woensdag 18 april 2007 @ 21:42:
Is het niet handiger om timestamps te gebruiken?

En dan vergelijken met de timestamps van de dag daarvoor, en daarvoor enz.

Nee, want als ik een nieuwe record met timestamp toevoeg voor elke hit, loopt de omvang van de database al snel enorm op. Ik heb er daarom voor gekozen om de aantallen hits, sessies etc. in een tabel te zetten die maximaal met 24 records per dag groeit: 1 record voor elk uur van de dag:

code:

+------------+------+-----+----------+
|date        | hour |hits | sessions |
+------------+------+----------------+
| 2007-04-18 |   13 |  47 |        4 |
+------------+------+----------------+
| 2007-04-18 |   14 |  43 |        3 |
+------------+------+----------------+
| 2007-04-18 |   15 |  22 |        2 |
+------------+------+----------------+
| 2007-04-18 |   16 |  89 |        6 |
+------------+------+----------------+

Ik denk dat dit een efficiente manier is, en wil graag deze tabel aanhouden

[ Voor 12% gewijzigd door Reveller op 18-04-2007 21:47 ]

[Verwijderd]

Tip: kijk eens naar INTERVAL in MySql

http://dev.mysql.com/doc/...e-and-time-functions.html

[Alain]

Volgens mij wordt de omvang daar alleen maar kleiner van, alleen je date record is nu al 11 bytes lang.

Overigens bedoelde ik dus een unix timestamp (unsigned integer) en niet het type timestamp.

[Creepy]

AlainS schreef op woensdag 18 april 2007 @ 21:42:
Is het niet handiger om timestamps te gebruiken?

En dan vergelijken met de timestamps van de dag daarvoor, en daarvoor enz.

Nee? MySQL heeft prima date functies om een vorige dag te bereken of een week uit een datum te halen etc. etc.

Een timestamp gebruiken om alleen een datum in op te slaan lijkt me niet nuttig. MySQL kan prima rekenen met de verschillende date en time types die er zijn dus dat werkt prima

Daarnaast wordt een date echt niet in een string opgeslagen en is dus echt geen 10 (11?) bytes lang. Het wordt alleen als string gerepresenteerd. Een timestamp bevat een datum en een tijd. Een date bevat alleen een datum. Lijkt me duidelijk dat een date niet meer ruimte zal innemen dan een timestamp

[ Voor 22% gewijzigd door Creepy op 18-04-2007 21:59 ]

[marty]

date is trouwens een gereserveerd woord en als je het dan toch als kolomnaam gebruikt zou je er dus backticks omheen moeten zetten

[Alain]

@Creepy: Bedankt, duidelijk.

[Reveller]

Verwijderd schreef op woensdag 18 april 2007 @ 21:50:
Tip: kijk eens naar INTERVAL in MySql

http://dev.mysql.com/doc/...e-and-time-functions.html

Dat was een goede tip Ik ben nu een heel eind met het maken van een query waarmee ik de laatste 7 dagen uit de database trek:

code:

+------------+-----+----------+
|date        |hits | sessions |
+------------+----------------+
| 2007-04-18 |  47 |        4 |
+------------+----------------+
| 2007-04-17 |  43 |        3 |
+------------+----------------+
| 2007-04-16 |  22 |        2 |
+------------+----------------+
| 2007-04-14 |  89 |        6 |
+------------+----------------+
| 2007-04-13 |  44 |        6 |
+------------+----------------+
| 2007-04-12 |  55 |        2 |
+------------+----------------+
| 2007-04-11 |  78 |        5 |
+------------+----------------+
| 2007-04-10 |  22 |        7 |
+------------+----------------+

SQL:

SELECT DAYNAME(date) AS day, COUNT(hits) AS hits 
FROM stats_dt 
WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE() 
GROUP BY date DESC

Resultaat:

PHP:

while ($day = db_fetch_object($result)) {
  $output.= "$day->day: $day->hits<br>";
}

// Wednesday: 47
// Tuesday: 43
// Monday: 22
// Saturday: 89
// Friday: 44
// Thursday: 55

Het probleem is dit: op 2007-04-15 (zondag) waren er geen bezoekers op de site, en er staan dus geen gegevens van deze dag in de database. Met de select query pak ik de laatste 7 dagen uit de tabel, ongeacht de eventuele "gaten" tussen die dagen. Wat ik graag zou willen, is dat als er een dag niet vermeld staat in de database, deze dag in de output komt met "0" hits. De output zoals ik die wil zou dus zijn:

PHP:

// Wednesday: 47
// Tuesday: 43
// Monday: 22
// Sunday: 0     <------ is erbij geplaatst
// Saturday: 89
// Friday: 44
//               <----- Thursday staat er niet meer bij; we hebben al 6 dagen

Mijn vraag is:

• Is het mogelijk de database via het SELECT statement te instrueren om ontbrekende dagen toe te voegen aan de resultset, zodat de resultset altijd de afgelopen dagen bevat, zonder "gaten" van dagen waarop er geen hits waren?
• Of moet ik deze dagen toevoegen in de PHP while-loop?
• Of moet ik via een cronjob of whatever ontbrekende dagen in de tabel toevoegen, zodat ook dagen waarop geen hits zijn, als record worden toegevoegd?

[P.O. Box]

Reveller schreef op woensdag 18 april 2007 @ 23:57:
[...]


Met de select query pak ik de laatste 7 dagen uit de tabel, ongeacht de eventuele "gaten" tussen die dagen.

Dat is niet waar... je neemt alleen de records van 7 dagen terug tot nu... als het nu dus donderdag 19 april is, dan neemt hij de records van 12 april t/m 19 april (Weet even niet meer of BETWEEN 12 april zelf ook meeneemt, volgens mij wel... als dat zou is moet je INTERVAL 6 DAY doen)... Als 13 en 14 april niet bestaan in de database gaat de selectie echt niet ineens de records van 10 april t/m 19 april laten zien...

het makkelijkste is om de gaten tussen de dagen via de PHP op te lossen... via de select is ook wel mogelijk, maar dat levert een vrij ingewikkelde, niet echt overzichtelijke query op....

een cronjob die dagelijks runt is ook een oplossing, maar daar zou ik zelf niet voor kiezen. Ik zou alle bewerkingen en selecties op de tabel namelijk lekker centraal in een paar PHP files houden en niet ook nog eens her en der cronjobs gaan starten...

succes!

[ Voor 6% gewijzigd door P.O. Box op 19-04-2007 09:14 ]

[Verwijderd]

Edwardvb schreef op donderdag 19 april 2007 @ 09:13:
[...]
Als 13 en 14 april niet bestaan in de database gaat de selectie echt niet ineens de records van 10 april t/m 19 april laten zien...

Eigenlijk is dat ook wel logisch Ben ik weer gerust gesteld...

het makkelijkste is om de gaten tussen de dagen via de PHP op te lossen... via de select is ook wel mogelijk, maar dat levert een vrij ingewikkelde, niet echt overzichtelijke query op....
[...]
een cronjob die dagelijks runt is ook een oplossing, maar daar zou ik zelf niet voor kiezen. Ik zou alle bewerkingen en selecties op de tabel namelijk lekker centraal in een paar PHP files houden en niet ook nog eens her en der cronjobs gaan starten...

Dat ben ik met je eens. Uit nieuwsgierigheid ben ik alleen wel benieuwd naar de query die hiervoor gebruikt zou kunnen worden, dus als iemand iets in die richting zou kunnen laten zien, dan graag!

succes!

Dank je...en bedankt voor je antwoord

[Janoz]

Op zich valt de query best mee. Je moet hem gewoon joinen met een hulp tabel waarin je alle datums hebt staan.

offtopic:
* Janoz wijst ACM nog ff op de topictitel en meld dat we het hier niet over een database hebben, maar over MySQL

[ Voor 39% gewijzigd door Janoz op 19-04-2007 13:24 ]

[ACM]

Janoz schreef op donderdag 19 april 2007 @ 10:13:
Op zich valt de query best mee. Je moet hem gewoon joinen met een hulp tabel waarin je alle datums hebt staan.

Getver. Maak dan een stored procedure die gegeven een begin- en einddatum een "tabel van datums" uitspuugt en join daar tegen. Maar je kan natuurlijk ook gewoon in je php-code ontdekken dat er een dag mist en de benodigde waardes op 0 stellen.

edit:

Blijkbaar kan mysql geen set-returning functions aan?

Overigens lijkt het me verstandiger om SUM in je query te gebruiken ipv COUNT

[ Voor 11% gewijzigd door ACM op 19-04-2007 12:30 ]

[Icelus]

Creepy schreef op woensdag 18 april 2007 @ 21:56:Daarnaast wordt een date echt niet in een string opgeslagen en is dus echt geen 10 (11?) bytes lang. Het wordt alleen als string gerepresenteerd. Een timestamp bevat een datum en een tijd. Een date bevat alleen een datum. Lijkt me duidelijk dat een date niet meer ruimte zal innemen dan een timestamp

TIMESTAMP = 4 bytes
DATE = 3 bytes
TIME = 3 bytes
DATETIME = 8 bytes