Tijden en tijdzones in MySQL en PHP

Door G Jansma op 05-06-2017 19:15

5.723 views

Hallo allemaal,

Ik heb een idee waarvan ik niet precies weet hoe ik dat het best kan uitwerken. Op mijn site wil ik wedstrijden laten zien die 'vandaag' worden gespeeld. Daarbij wil ik dan de tijd laten zien dat de wedstrijd begint voor de bezoeker en kunnen laten zien dat de wedstrijd bezig is of af is. Eventueel met een optie om dat om te zetten naar lokale tijden.

In de database wil ik dus de datum en tijd bewaren van een wedstrijd, ook plaats + land. Het punt is alleen dat die wedstrijden over de hele wereld plaats vinden, dus ik vermoed dat ik dan met tijdzones moet gaan rekenen? En speelt zomer- of wintertijd dan ook nog een rol? Ik raak een beetje verstrikt hoe ik dat moet uitwerken, zowel qua opslag in MySQL als hoe ik dat in PHP vervolgens moet gebruiken. Ik ben wel bekend met de PHP timezone functie op UTC om tijdverschillen te berekenen.

Hopelijk kunnen jullie me op weg helpen.

Ben van Velzen

06-06-2017 11:26

>> Een bijkomend probleem waar ik tegen aanloop is dat je met PHP kennelijk niet de tijdzone van een bezoeker kan achterhalen?

Correct, hiervoor zou je naar een third party moeten gaan. De google maps API bijvoorbeeld heeft timezone offsets om te gebruiken.

Binnen mysql zou je iets kunnen doen als:


SET time_zone = 'Europe/Amsterdam';

G Jansma

06-06-2017 22:19

@Ward: Aardig punt, maar voorlopig is dat nog niet echt wat ik nodig heb. Wedstrijden die geweest zijn zijn in tegenstelling tot films namelijk nog wel interessant. Maar ik zal het in het achterhoofd houden.

@Ben: Ik snap niet zo goed wat die SET time_zone kan doen als ik de datum en tijdzone afzonderlijk ophaal uit Mysql? Wat betreft die API is dat misschien een optie, al ben ik tot dusver niet zo bekend met Javascript en kan ik die API's soms maar moeilijk doorgronden.

Maar wat denken jullie van het scriptje? Is dit de way to go?

- SanThe -

07-06-2017 12:48 gewijzigd op 07-06-2017 12:51

Het is slechts een testopzet.
Misschien een beetje slordig nog.
Maar het werkt.

Is de tekst in het overzicht groen dan is de wedstrijd bezig.
Is het rood dan is die voorbij.

Uiteraard moet je het geheel aan jouw eisen e.d. aanpassen.


<?php
/*
Database			Test@2017

Tabel timezones
	id				INT			AUTO_INCREMENT
	zone			VARCHAR		40
	
Tabel wedstrijden
	id				INT			AUTO_INCREMENT
	naam			VARCHAR		40
	begintijd		DATETIME
	eindtijd		DATETIME
	plaats			VARCHAR		40
	zone			INT
*/
ini_set('display_errors',1);
error_reporting(E_ALL);

date_default_timezone_set('Europe/Amsterdam');

session_start();
$maxSteden = 5;

$siteMySQL['host']					= 'localhost';
$siteMySQL['user']					= 'Test@2017';
$siteMySQL['pass']					= 'Test@2017';
$siteMySQL['db']					= 'Test@2017';
$mysqli = @mysqli_connect($siteMySQL['host'], $siteMySQL['user'], $siteMySQL['pass'], $siteMySQL['db']);

# Errorafhandeling: Kan je iets moois van maken
function showError($regel, $error)
{	exit('Error op regel '.$regel.': '.$error);
}

# Alle bestaande timezones uit de database halen
$sql = "	SELECT	zone
			FROM	timezones
		";
if(($result = @mysqli_query($mysqli, $sql)) === false)
{	showError(__LINE__, mysqli_error($mysqli));
}

$checkTimezonesArray = array();
while($row = @mysqli_fetch_assoc($result))
{	$checkTimezonesArray[] = $row['zone'];
}

# Alle bestaande timezones van PHP ophalen
$phpTimezonesArray = timezone_identifiers_list();

# Javascript voor het formulier
?>
<script type="text/javascript">
	phpTimezonesArray = [];
	function setSelect(value)
	{	for(i=0;i<phpTimezonesArray.length;i++)
		{	if(phpTimezonesArray[i].plaats.toLowerCase().indexOf(value.toLowerCase()) >= 0)
			{	document.getElementById('plaats').value = phpTimezonesArray[i].plaats;
				document.getElementById(phpTimezonesArray[i].zone).selected = 'true';
			}
		}
	}
	function setStad(value, id)
	{	for(i=0;i<phpTimezonesArray.length;i++)
		{	if(phpTimezonesArray[i].plaats.toLowerCase().indexOf(value.toLowerCase()) >= 0 && value != '')
			{	document.getElementById('stad'+id).value = phpTimezonesArray[i].plaats;
			}
		}
	}
</script>
<?php
foreach($phpTimezonesArray as $phpTimezone)
{	# Geef de timezones door aan Javascript
	echo '<script type="text/javascript">';
		$hulp = json_encode(array('plaats'=>substr(strrchr($phpTimezone, '/'), 1), 'zone'=>$phpTimezone));
		echo 'phpTimezonesArray.push(JSON.parse(\''.$hulp.'\'));';
	echo '</script>';
	# Check of de PHP-timezone al in de database zit
	if(!in_array($phpTimezone, $checkTimezonesArray))
	{	# Zo niet INSERT
		echo $phpTimezone.' is toegevoegd.<br/>';
		$sql = "	INSERT INTO timezones 
					(
						zone
					) 
					VALUES 
					(
						'".@mysqli_real_escape_string($mysqli, $phpTimezone)."'
					)
				";
		if(@mysqli_query($mysqli, $sql) === false)
		{	showError(__LINE__, mysqli_error($mysqli));
		}
	}
}

# Wedstrijdenoverzicht met de tijden uit de timezone
function showWedstrijden($zone='Amsterdam')
{	global $mysqli;
	$sql = "	SELECT	zone
				FROM	timezones
				WHERE	zone LIKE '%/".@mysqli_real_escape_string($mysqli, $zone)."'
			";
	if(($result = @mysqli_query($mysqli, $sql)) === false)
	{	showError(__LINE__, mysqli_error($mysqli));
	}
	$row = @mysqli_fetch_assoc($result);
	if(@mysqli_num_rows($result) > 0)
	{	$timezone = $row['zone'];
	}
	$sql = "	SELECT	w.naam, w.begintijd, w.eindtijd, w.plaats, t.zone
				FROM	timezones as t, wedstrijden as w
				WHERE	w.zone = t.id
				ORDER BY w.begintijd ASC
			";
	if(($result = @mysqli_query($mysqli, $sql)) === false)
	{	showError(__LINE__, mysqli_error($mysqli));
	}
	date_default_timezone_set($timezone);
	echo '<br/><strong>Tijdzone '.$timezone.'</strong> '.date('d-m-Y H:i').'<br/>';
	while($row = @mysqli_fetch_assoc($result))
	{	date_default_timezone_set($timezone);
		$now = date('YmdHis');
		date_default_timezone_set('Europe/Amsterdam');
		$begintijd = strtotime($row['begintijd']);
		$eindtijd = strtotime($row['eindtijd']);
		date_default_timezone_set($timezone);
		$style = ($now > date('YmdHis',$begintijd)) ? ' style="color:green;"' : '';
		$style = ($now > date('YmdHis',$eindtijd)) ? ' style="color:red;"' : $style;
		echo '<span'.$style.'>'.date('d-m-Y H:i',$begintijd).' '.date('d-m-Y H:i',$eindtijd).' '.$row['naam'].' '.$row['plaats'].' '.$row['zone'].'</span><br/>';
	}
}

# Afhandeling formulieren
if($_SERVER['REQUEST_METHOD'] == "POST" and isset($_POST['form']))
{	switch($_POST['form'])
	{	case 'wedstrijd'	:
			# Alle tijden worden in timezone Europe/Amsterdam in de databse gezet
			$sql = "	SELECT	id, zone
						FROM	timezones
						WHERE	zone = '".@mysqli_real_escape_string($mysqli, $_POST['zone'])."'
					";
			if(($result = @mysqli_query($mysqli, $sql)) === false)
			{	showError(__LINE__, mysqli_error($mysqli));
			}
			$row = @mysqli_fetch_assoc($result);
			date_default_timezone_set($row['zone']);
			$begintijd = strtotime($_POST['begintijd']);
			$eindtijd = strtotime($_POST['eindtijd']);
			date_default_timezone_set('Europe/Amsterdam');
			$sql = "	INSERT INTO wedstrijden 
						(
							naam,
							begintijd,
							eindtijd,
							plaats,
							zone
						) 
						VALUES 
						(
							'".@mysqli_real_escape_string($mysqli, $_POST['naam'])."',
							'".date('Y-m-d H:i:00', $begintijd)."',
							'".date('Y-m-d H:i:00', $eindtijd)."',
							'".@mysqli_real_escape_string($mysqli, $_POST['plaats'])."',
							'".$row['id']."'
						)
					";
			if(@mysqli_query($mysqli, $sql) === false)
			{	showError(__LINE__, mysqli_error($mysqli));
			}
		break;
		case 'overzicht'	:
			# De steden voor het overzicht in de session zetten
			for($i=1;$i<=$maxSteden;$i++)
			{	$_SESSION['stad'.$i] = (empty($_POST['stad'.$i])) ? '' : $_POST['stad'.$i];
			}
		break;
	}
}
# De formulieren
?>
<div style="float:left;">
	<strong>Ingeven van een wedstrijd</strong><br/>
	<form action="" method="post">
		<input type="hidden" name="form" value="wedstrijd"/>
		<input name="naam"/> Naam Wedstrijd<br/>
		<input id="plaats" list="plaatsen" name="plaats" autocomplete="off" onchange="setSelect(this.value);"/> Naam van de Stad<br/>
			<datalist id="plaatsen">
				<?php
				foreach($phpTimezonesArray as $phpTimezone)
				{	echo '<option value="'.(substr(strrchr($phpTimezone, '/'), 1)).'">';
				}
				?>
			</datalist>
		<input name="begintijd"/> Locale Begin Datum en Tijd (YYYY-MM-DD HH:MM)<br/>
		<input name="eindtijd"/> Locale Eind Datum en Tijd (YYYY-MM-DD HH:MM)<br/>
		<select name="zone">
			<option id="leeg"></option>
			<?php
			foreach($phpTimezonesArray as $timezone)
			{	echo '<option id="'.$timezone.'">'.$timezone.'</option>';
			}
			?>
		</select> Tijdzone<br/>
		<input type="submit"/><br/><br/>
	</form>
</div>

<div style="float:right;text-align:right;">
	<strong>Steden van het overzicht</strong><br/>
	<form action="" method="post">
		<input type="hidden" name="form" value="overzicht"/>
		<?php
		for($i=1;$i<=$maxSteden;$i++)
		{	echo 'Stad '.$i.' <input id="stad'.$i.'" list="plaatsen" name="stad'.$i.'" autocomplete="off" onchange="setStad(this.value, '.$i.')" value="'.(isset($_SESSION['stad'.$i]) ? $_SESSION['stad'.$i] : '').'"/><br/>';
		}
		?>
		<input type="submit"/><br/><br/>
	</form>
</div>

<div style="clear:both;"></div>
<?php
# De overzichten
for($i=1;$i<=$maxSteden;$i++)
{	if(!empty($_SESSION['stad'.$i]))
	{	echo '<div style="margin:0px auto;width:600px;">';
			showWedstrijden($_SESSION['stad'.$i]);
		echo '</div>';
	}
}
?>

G Jansma

07-06-2017 23:09

Wow, wat een uitgebreid script. Ik kom hier zeker op terug als ik de tijd heb om dit even allemaal uit te vogelen. Enorm bedankt alvast!

- SanThe -

07-06-2017 23:16

Graag gedaan.

Ik was zelf ook wel nieuwsgierig geworden hoe dit nou zou moeten werken. Het was leuk en leerzaam om het uit te zoeken. Het werkt hier perfect.

Ik hoor het wel als er vragen zijn.

G Jansma

13-06-2017 18:27

Ik heb het allemaal bekeken, en het werk erg mooi. Bedankt daarvoor! Het enige probleem waar ik nog tegen aan loop is dat ik direct wil nagaan in welke tijdzone iemand zit. Nu voer je de locaties zelf in, maar dat is dus iets wat ik niet wil. Enig idee hoe dat het best zou kunnen?

Frank Nietbelangrijk

13-06-2017 22:05

Het is internet he. Een request kan uit elke uithoek van de wereld komen maar ziet er altijd hetzelfde uit. De enige kleine houvast is een ip adres. Zie https://stackoverflow.com/questions/743505/how-to-get-time-zone-through-ip-address-in-php. Maar ook hier zitten nog haken en ogen aan.

Wil je de gebruiker op maat gesneden tijden laten zien dan zou ik er toch voor kiezen om een account aan te bieden aan de gebruiker.

Thomas van den Heuvel

14-06-2017 00:45

Cookie niet goed genoeg om een voorkeurstijdzone te onthouden? :/

Ariën

14-06-2017 00:54 gewijzigd op 14-06-2017 01:01

Cookies voor tijdzones? Na een inlog elders, of bij het cleanen van je cookies sta je weer op UTC.
Als je al een useraccount-systeem hebt, dan kan je de tijdzone prima opslaan. Je moet wel rekening houden met de wintertijd, want dan verandert de uur-interval ook. Of sla in zijn profiel op in welk land hij woont, en stem de tijdzones daarop af.

Ik denk dat grotere sites wel met landherkenning werken en Geo2IP databases, en alle landen per tijdzone hebben ingedeeld. Er is zelfs een complete dataset voor in diverse formaten.

G Jansma

26-08-2017 15:48

Ik dacht dat ik het allemaal perfect voor elkaar had, maar loop toch weer tegen een probleem aan met mijn tijdzone.

Ik selecteer namelijk op basis van de datum van de tijdzone, en dan op de wedstrijden die dag. Maarja, als je dan in Australië woont ben je dus al een dag verder terwijl er in de USA nog wedstrijden moeten worden verreden. Niet zo slim dus. Precies het probleem wat Ward hier al eerder schetste met een tv-gids. Nu krijg je dus afhankelijk van je tijdzone wisselende resultaten, maar dat moet dus eigenlijk niet nee.

Maar kan ik vanuit Mysql zelf al ophalen welke wedstrijden er de komende 24 uur (en afgelopen 12 uur) zijn? Aangezien de tijden allemaal UTC (?) zijn, en die vervolgens met tijdzone worden omgezet naar de daadwerkelijke tijd. Of moet ik dat eerst met PHP ophalen, de tijden omzetten, en daarmee opnieuw de betreffende wedstrijden ophalen uit Mysql?

Reageren

Inloggen om te reageren