Control date between client and server in PHP

3

I have made a small event registration module, taking between date and time fields: YY-mm-dd H:i:s . Users who register can be from different time zones. Then an event scheduled for a moment could not correspond with the other clients.

Basically the question would be: how to convert data from "date and time" in "client time zone" to "server time zone" to save in BD.

And vice versa: how to convert data from "date and time" in "server time zone" to "client time zone"?

I use a Mysql database server.

    
asked by Yenny Farfan 08.02.2018 в 04:22
source

1 answer

3

Dates in databases must always be in UTC (Coordinated Universal Time) or in UNIX time stamp (UNIX timestamp, independent of the time slot) and convert to local time or on the client side using javascript or in the server side using, in your case, PHP.

Using PHP

PHP provides the necessary tools to convert dates or timestamps to local format:

  • time() : Get a UNIX timestamp.
  • date_default_timezone_set() : Sets the local time slot with which the following functions work.
  • date() : converts the current local date or UNIX timestamp to a string with the desired format.
  • setlocale() : set the local language settings for dates, numbers, etc.
  • strftime() : converts the current local date or the UNIX timestamp to a string of the desired format with support for local languages on behalf of months, days, etc.

Basically what you have to do is decide if you store UNIX time stamps or dates and times in UTC in your database. Once you have decided you can use date() and / or strftime() to show these dates in the language and time zone of the user (you can get it through javascript in the login screen, for example) or static defined in your server (in php.ini ) or programmed in code.

Example:

<?php
date_default_timezone_set('Europe/Madrid');
setlocale(LC_ALL, 'es_ES.utf8', '[email protected]', 'es_ES', 'es');
/* 2013-01-09T02:53:20+00:00 en ISO-8601 */
$tiempo = 1357700000;
echo strftime("En España era el %A %e de %B de %Y a las %H horas, %M minutos y %S segundos", $tiempo), PHP_EOL;
date_default_timezone_set('America/Argentina/Buenos_Aires');
echo strftime("En Argentina (Buenos Aires) era el %A %e de %B de %Y a las %H horas, %M minutos y %S segundos", $tiempo), PHP_EOL;

Execution result:

  

In Spain it was Wednesday, January 9, 2013 at 03:00, 53 minutes and 20 seconds.

     

In Argentina (Buenos Aires) it was Tuesday, January 8, 2013 at 23 hours, 53 minutes and 20 seconds.

Using Javascript

Javascript has basic support for formatting local dates and natively only manages the time slot.

To create a date Date Javascript from a brand of UNIX time you have to multiply this by 100:

function timestamp2cadena(timestamp){
  var date = new Date(timestamp * 1000);
  var meses = [
    'enero',
    'febrero',
    'marzo',
    'abril',
    'mayo',
    'junio',
    'julio',
    'agosto',
    'septiembre',
    'octubre',
    'noviembre',
    'diciembre'
  ];
  var año = date.getFullYear();
  var mes = meses[date.getMonth()];
  var día = date.getDate();
  var hora = date.getHours();
  var minutos = date.getMinutes();
  var segundos = date.getSeconds();
  return 'En horario local del navegador era el ' +
    día + ' de ' + mes + ' de ' + año + ' a las ' + hora + ' horas, ' + minutos + ' minutos y ' + segundos + ' segundos';
}
window.addEventListener("load", function() {
  /* 2013-01-09T02:53:20+00:00 en ISO-8601 */
  document.getElementById('resultado').innerText = timestamp2cadena(1357700000);
});
<p id="resultado">A rellenar por javascript</p>

The result in my browser (being in Spain) is:

  

The local browser time was January 9, 2013 at 3 hours, 53 minutes and 20 seconds.

Using MySQL

MySQL also supports managing slots and converting dates to different locales even if you need an additional step that allows you to manage, for example, winter / summer timetables.

This example shows how you can convert a date between different hours (without taking into account summer time):

CREATE TABLE pruebas (
  timestamp INT
);
SET time_zone = '+00:00'; -- Introducimos las fechas en UTC
INSERT INTO pruebas (timestamp) VALUES (
  1357700000
), ( -- Año no bisiesto, el día anterior es el 28 de febrero
  UNIX_TIMESTAMP(STR_TO_DATE('2018-03-01 02:56:23', '%Y-%m-%d %H:%i:%S'))
), ( -- Año bisiesto, el día anterior es 29 de febrero
  UNIX_TIMESTAMP(STR_TO_DATE('2012-03-01 00:12:46', '%Y-%m-%d %H:%i:%S'))
);
SET time_zone = '+01:00'; -- Franja actual de Madrid
SELECT timestamp, FROM_UNIXTIME(timestamp) fecha FROM pruebas;
SET time_zone = '-03:00'; -- Franja actual de Buenos Aires
SELECT timestamp, FROM_UNIXTIME(timestamp) fecha FROM pruebas;

You can see the example by working online by clicking here .

Result:

timestamp   fecha
1357700000  2013-01-09 03:53:20
1519872983  2018-03-01 03:56:23
1330560766  2012-03-01 01:12:46

timestamp   fecha
1357700000  2013-01-08 23:53:20
1519872983  2018-02-28 23:56:23
1330560766  2012-02-29 21:12:46
    
answered by 08.02.2018 / 08:53
source