SQL date format

Question

SQL date format

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I have created the following table in a SQL database:

create table session(
     SessionId int auto_increment unique,
     SessionIni datetime,
     SessionFin datetime,
     UsHGTAG int,
     PcId int,
     primary key (SessionId)
);

How do I get PHP to capture the data of a HTML form in the appropriate año/mes/dia hora format so that I can save it in the session table in the fields sessionIni and SessionFin and then perform time calculations with them?

Already the PHP file is correctly connected to index.html and the database.

The problem is that the date format that the input brings in HTML is day / month / year also does not include the time because it is an input type:date .

sql html php date input

asked by Lsickle 28.08.2018 в 07:31

source

2 answers

2

<?php date_default_timezone_set('America/Bogota'); $fecha = date('Y-m-d'); ?>

Or completely: <?php date_default_timezone_set('America/Bogota'); $fecha = date('Y-m-d h:i:s ', time()); ?>

The variable "$ date", you put it in the element you need and it already has the default date.

answered by 12.01.2019 в 19:26

Error inserting records in SQL Server (BULK INSERT) Life time of an object [closed]

score 1 · Accepted Answer

Although the value displayed by the browser is based on the local configuration of the browser, in fact the data that will be sent will ALWAYS be coded as AAAA-MM-DD :

It should be noted that the date format shown differs from the value itself: the date format displayed will be chosen based on the user's browser's regional settings, although the value date is always formatted as aaaa-mm-dd .

It's just the one MySQL requires for its fields DATETIME :

MySQL recognizes values DATE in the following formats:


As a string in 'AAAA-MM-DD' or 'AA-MM-DD' .



[...]



MySQL recognizes values DATETIME and TIMESTAMP in the following formats:


Like a string in 'AAAA-MM-DD HH:MM:SS' or 'AA-MM-DD HH:MM:SS' .



[...].

Entering a value AAAA-MM-DD in a field of type DATETIME will result in a conversion of type DATE to DATETIME , setting the hour, minutes and seconds to 0 ( AAAA-MM-DD 00:00:00 ).

You can concatenate both values in PHP to generate a string of the last type:

<?php
$datetime = $_POST['fecha'] . ' ' . $_POST['hora'];

Although you should keep in mind that this date will be in local time slot, so you will have to take it into account by sending the user's strip to store the dates in the database in UTC whenever possible.

Here is a practical example showing that the format sent will be correct:

function comprueba(form) {
  /* Calculamos la fecha en formato UTC. La sustitución
      de T por " " no es estrictamente necesaria */
  form.datetime.value = new Date(
    form.fecha.value + ' ' + form.hora.value
  ).toISOString().substr(0,19).replace('T', ' ');
  console.log('Fecha: ', form.fecha.value);
  console.log('Hora: ', form.hora.value);
  console.log('Datetime en UTC: ', form.datetime.value);
  return false;
}
window.onload = function() {
  /* Ponemos la fecha y hora actual */
  var fecha = new Date();
  document
    .querySelector('input[type="date"]')
    .value = fecha.toISOString().substr(0,10);
  document
    .querySelector('input[type="time"]')
    .value = fecha.toTimeString().substr(0, 5);
}

<form onsubmit="return comprueba(this)">
  <input type="date" name="fecha" />
  <input type="time" name="hora" />
  <input type="hidden" name="datetime" />
  <input type="submit" />
</form>