DATETIME in MySQL does not correctly record the date

0

I have the following problem. I use the datetimepicker library to select a date in a form and register it in the database. The problem is that I want to register the date in the MySQL format but I do not record the time, that is, I select the format in this way on 2017-09-28 11:32:34 but in the database I only register 2017-09-28 00:00:00

It should be noted that I made a var_dump in PHP and if you print the date with all of the time selected, then I do not understand why it caused the error, also commenting that the data type I use in MYSQL is DATETIME

//Seccion Insertar

if($page=='create'){
    if($_POST) {
        $validator = array('success' => false, 'messages' => array());
        if(isset($_SESSION['u_usuario']['PK_idUsuario'])){
            #Obtener el Usuario que inició sesion para saber quién está manipulando los tickets.
            $usuarioCreado = $_SESSION['u_usuario']['PK_idUsuario'];
            $fechaVencimiento = $_POST["fechaFinal"];

            $color = $_POST["colorNota"];
            $regDescripcion = $_POST["regDescripcion"];
            $status = 1;

            $sql="INSERT INTO recordatorio (fechaVencimiento, descripcion, status, color, FK_idUsuario) VALUES ('$fechaVencimiento','$regDescripcion','$status', '$color', '$usuarioCreado')";              
            $query = $con->query($sql);         
            var_dump($sql);

            if($query === TRUE ) {
                $validator['success'] = true;
                $validator['messages'] = "Recordatorio creado correctamente";               

            } else {
                $validator['success'] = false;
                $validator['messages'] = "Error al crear el recordatorio";                  
            }
        }else{
            $validator['success'] = false;
            $validator['messages'] = "Sesion caducada, recargue la página e inicie sesion nuevamente para registrar un nuevo recordatorio.";
        }

        $con->close();
        echo json_encode($validator);
    }
}


#Seccion Recuperar
else if($page=='read'){
    $output = array('data' => array());
    $query = $con->query("SELECT * FROM recordatorio WHERE status = 1 ORDER BY fechaVencimiento ASC");

    $x = 1;

    while ($row = $query->fetch_assoc()){
        #Almacenar los datos en el arreglo.
        $btnEvento ='
        <button data-toggle="modal" data-target="#modaleditarRecordatorio" type="button" class="btn btn-info btn-sm" title="Editar" onclick="editarRecordatorio('.$row['PK_idRecordatorio'].')"><i class="fa fa-pencil"></i></button>
        <button data-toggle="modal" data-target="#modaleliminarRecordatorio" type="button" class="btn btn-danger btn-sm" title="Eliminar" onclick="eliminarRecordatorio('.$row['PK_idRecordatorio'].')"><i class="fa fa-trash"></i></button>                
        ';


        $descripcion = '<p style="color:#0088cc">'.$row['descripcion'].'</p>';
        $output['data'][] = array(
            $x,
            $descripcion,
            $row['fechaInicio'],
            $row['fechaVencimiento'],
            $btnEvento
        );
    $x++;
    }
    $con->close();
    echo json_encode($output);
}
    
asked by Silvestre Silva 28.09.2017 в 18:27
source

1 answer

0

The date format for a datetime field in MySQL is:

AÑO-MES-DIA HORA:MINUTOS:SEGUNDOS

So, as suggested by Mario Alejandro Gonzalez Flore, you should format your insertion date well. In your specific case it would be to change the following line:

$fechaVencimiento = date('Y-m-d H:i:s', strtotime($_POST["fechaFinal"]));

Or if you want to leave that task to MySQL:

$fechaVencimiento = "STR_TO_DATE(".$_POST["fechaFinal"].", '%d/%m/%Y %H:%i:%s')";

And where does it say "% d /% m /% Y% H:% i:% s" you can specify how you have the original format.

To also say that it is not a good idea to place a POST directly in the SQL without trying since there is the possibility of SQL Injection attacks, I recommend you see the following link:

How to avoid injection SQL in PHP?

    
answered by 30.04.2018 в 11:45