PHP / MySQL: πρόβλημα με ημερήσια (γκρουπαρισμένα) στατιστικά και timezone (πχ Ελλάδας)

[philos]

Θα περιγράψω όσο πιο απλά μπορώ το πρόβλημά μου, καθώς και με chatgpt δεν έχω βγάλει άκρη και για άλλη μια μέρα πήγε 00:00 και το bug υπάρχει.

Έχω μια μειωμένη κατανόηση του θέματος timezone.

Το λοιπόν, εμφανίζω γκρουπαρισμένα daily στατιστικά μηνυμάτων (πίνακας xf_post, έχει τα μηνύματα, το post_date είναι unix time) με ένα $start κι ένα $end unix time.

Δηλαδή τις τελευταίες 7 ημέρες ξέρω εγώ:

$stat_days = 7;
$start = time() - ($stat_days * 86400);
$end = time();

Τα θέλω οπωσδήποτε στο timezone του χρήστη. Αυτό σημαίνει ότι όταν πχ στην Ελλάδα έχουμε 00:00 θα πρέπει να δείξει 0 μηνύματα ή τεσπα αν κάνω ένα μήνυμα στις 00:01 θα πρέπει να δείξει counter = "1 μήνυμα", να κάνει reset δηλαδή.

Ένα παράδειγμα query είναι αυτό (ξέρω τραβηγμένη SELECT, με chatgpt την έκανα ) :

        $stats = $db->query("SELECT
                UNIX_TIMESTAMP(DATE(CONVERT_TZ(FROM_UNIXTIME(xf_post.post_date), '+00:00', '+02:00'))) AS stats_date,
                COUNT(*) AS counter
            FROM
                xf_post
            WHERE
                xf_post.user_id = ? 
                AND xf_post.message_state = 'visible'
                AND xf_post.post_date BETWEEN ? AND ?
            GROUP BY
                stats_date
            ORDER BY
                stats_date DESC;", [$this->forUser->user_id, $this->start, $this->end]);

Κι έτσι φτιάχνω τον $output:

            while ($stat = $stats->fetch())
            {
                $output[$stat['stats_date']]['post'] = $stat['counter'];
            }

Να σημειώσω ότι έχω πειράξει την ας την πούμε setDateRange μέθοδο και πειράζω τo $this->end ως εξής:

	public function setDateRange($start, $end)
	{
		$start = intval($start);
		$end = intval($end);

		if ($end < $start)
		{
			$end = $start;
		}

        $timezone = new \DateTimeZone($this->forUser->timezone);
          
        $endDate = new \DateTime();
        $endDate->setTimestamp($end);
        $endDate->setTimezone($timezone);
        
        // μέχρι το τέλος της ημέρας
        $endDate->setTime(23, 59, 59);
        $end = $endDate->getTimestamp();

		$this->start = $start;
		$this->end = $end;
	}

Τι πάει λάθος και βλέπω λάθος στατιστικά;

Δεν είναι τόσο το πρόβλημα οι ενδιάμεσες μέρες, όσο ότι όταν αλλάζει η μέρα, αν κάνω ένα post στις 00:01, αυτό δεν φαίνεται και εξακολουθεί να το προσθέτει στην προηγούμενη ημέρα.

Κάποιος να κανονικοποιήσει το παραπάνω πρόβλημα;

[geomagas]

Δεν το έψαξα πολύ, απλά έχω μια υποψία.

Η FROM_UNIXTIME() κάνει τη μετατροπή σύμφωνα με το timezone του session του mysql server, που μπορεί να μην είναι +00:00 που υποθέτεις στην CONVERT_TZ(). 

Δoκίμασε 

          UNIX_TIMESTAMP(DATE(CONVERT_TZ(FROM_UNIXTIME(xf_post.post_date), @@session.time_zone, '+02:00'))) AS stats_date

Επίσης, αν κρατάς το tz του χρήστη στον αντίστοιχο πίνακα (που μάλλον ισχύει), κάνε ένα JOIN με αυτόν και αντικατέστησε το '+02:00' με την αντίστοιχη στήλη.

[philos]

Thanks θα το δω αύριο που θα ξυπνήσω!

Για αρχή έλεγα μήπως το πρόβλημα είναι και στη WHERE μου, δηλαδή στο:

AND xf_post.post_date BETWEEN ? AND ?

... επειδή στη WHERE δεν κάνω καμία μετατροπή του post_date και ίσως δεν τα πιάνει. Όμως θα μου πεις, κάνω απευθείας τις μετατροπές στις παραμέτρους της php start / end.

 

[geomagas]

Όχι, εφόσον κρατάς το post_date σαν timestamp (πολύ σωστη επιλογή!) και στέλνεις τις παραμέτρους σου πάλι σε timestamp, δεν θα έχεις πρόβλημα.

Τον php κώδικα δεν τον κοίταξα καθόλου, υποθέτω ότι περνάς αυτά ακριβώς που θέλεις...

Μια λεπτομέρεια πάντως: Η BETWEEN είναι inclusive κι από τις δύο μεριές. Αυτό σημαίνει ότι τα BETWEEN 1 AND 5 και BETWEEN 5 AND 10 θα περιέχουν αμφότερα το 5. Έχε το κατά νου.

[tsofras]

 Δοκίμασες το απλό debug, να φτύσεις σε μία κονσόλα το query που καταλήγει να τρέξει ?

Η εμπειρία λέει ότι τις περισσότερες φορές δεν φταίει ο κώδικας αλλά το πώς έχει φτιαχτεί δυναμικά το query

Οπότε προτείνω να το λογκαρεις και απλά να το τρέξεις

Θεωρητικά κάτι χάνεται από τις δηλώσεις Date, Unix timestamp ή την ώρα κατά την μετατροπή