CODEWITHSUNDEEP
phpmysqlsqldatedatetime
hormigaz
hormigaz

Reputation: 167

Get datetime field value from MySQL database

I'm trying to get a datetime value from MySQL table. I stored it like 'yyyy-mm-dd hh:mm:ss', but when I want to get it to print in a page, I get this '2019-04-01 00:00:00' or only '2019-04-01'. If I check the database, I can see the time stored properly, but when I want to print it, I only get zeros.

if( $stmt = $mysqli->prepare( '
    SELECT
        estadistica_id, 
        aprobados, 
        suspendidos, 
        pendientes, 
        envios, 
        fecha, 
        curso.curso_id, 
        identificador_envio, 
        curso.titulo AS titulo_curso
    FROM estadistica
    LEFT JOIN curso ON estadistica.curso_id = curso.curso_id
    ORDER BY titulo_curso ASC' . $order_by
) ) {
    if( true === $stmt->execute() ) {
        $stmt->store_result();
        $stmt->bind_result( 
            $estadistica_id, 
            $aprobados, 
            $suspendidos, 
            $pendientes, 
            $envios, 
            $fecha, 
            $curso_id, 
            $identificador_envio, 
            $titulo_curso 
        );
        if( ( $num_rows = $stmt->num_rows ) > 0 ) {
            while( $stmt->fetch() ) {
                echo $fecha;
            }
        }
        $stmt->free_result();
        $stmt->close();
    }
}

$fecha prints '2019-04-01' and nothing more. I don't know how to print the date and time stored in the database.

Here goes the screenshots of the database, where you can see that the date and time is stored correctly.

database1 database2

I'm lost :(

Appreciate all your help ;)

Upvotes: 0

Views: 825

Answers (2)

GMB
GMB

Reputation: 222462

When fetching a datetime value from database, the default format depends on your client. Most clients use MySQL default format ('YYYY-MM-DD HH24:MI:SS'), but it looks like yours doesn't.

One solution would be to turn the date to a string with the expected format within the SQL query, using MySQL function DATE_FORMAT():

Consider:

SELECT
    estadistica_id, 
    ..., 
    DATE_FORMAT(fecha, '%Y-%m-%d %H:%i:%s'),
    ...
FROM estadistica
...

Upvotes: 1

micster
micster

Reputation: 758

As R.Smith said in his comment, it should work if your column's format is varchar. You must have another one.

You can use DateTime (https://www.php.net/manual/en/class.datetime.php)

Example :

$date = new \DateTime($fecha);
echo $date->format('Y-m-d H:i:s');
// => 2019-04-01 18:42:03

Upvotes: 1

Related Questions