Convert date type 2016-10-04 15:51:16 to, 04-10-2016 15:51:16 to SQL or PHP how to do?

Question

I’m doing a job to show the date and time of insertion in the script in the database, I have to show the date in the dd-mm-yyyyyy format and then the time, can I do it by the script’s SELECT or by PHP? If there’s any way anyone can help me with that?

Remarks: I am using Phpmyadmin, the column that is the date is of type datetime. I am also using the PDO.

<?php
            try {
                $pdo = new PDO('mysql:host=localhost;dbname=agenda', 'root', '');
                 // define para que o PDO lance exceções caso ocorra erros
                $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);                    

            } catch (Exception $exc) {
                echo "Algum erro foi cometido".$exc->getTraceAsString();                         
            }

            $id = $_GET['id'];
            $buscasegura= $pdo->prepare("SELECT * FROM  pessoa WHERE id = :id");
            $vertel = $pdo->prepare("SELECT * FROM `contatos` WHERE id_pessoa = :id");

            $buscasegura->bindValue(":id", $id);
            $vertel->bindValue(":id", $id);

            $buscasegura->execute();
            $vertel->execute(); 

            while($linharesp=$buscasegura->fetch(PDO::FETCH_ASSOC)){
        ?>
        <div class="container  body321">
            <div class="row">                            
                <h4>Ver informações de : <?php echo $linharesp["nome"];}?> </h4>
                <?php                
                        $verregistro = $pdo->prepare('SELECT * FROM `registro` WHERE id_pessoa = :id');
                        $verregistro->bindParam(':id',$id);
                        $verregistro->execute();                                            
                    ?><br>
                <h4>&nbsp;&nbsp;&nbsp;Informações de registros</h4>
                <div class="col-md-6">

                    <?php
                        while($linharesp=$verregistro->fetch(PDO::FETCH_ASSOC)){ 
                            $solucao = $linharesp['resolvido'];
                            if(strcasecmp( $solucao, 'sim' ) == 0){
                                $class = "alert-success";
                            }else{
                                $class = "alert-danger";
                            } 

                    ?>     
                    <div class="info2 <?php echo $class; ?>">                        
                        <span class="p">Forma de Comunicação:</span>
                            <?php echo $linharesp['info']; ?>
                        <span class="p">Assunto:</span>
                            <?php echo $linharesp['assunto']; ?>
                        <span class="p">Descrição:</span>
                            <?php echo $linharesp['descricao']; ?>
                        <span class="p">Resolvido?</span>
                            <?php echo $solucao;?> 
                    </div><br>
                    <div class="text-right">
                        <span class="p">Data/Hora:</span>
                        <?php echo $linharesp['data']; ?>
                        <a class="label label-danger text-right" href="#">Delete</a>
                    </div>
                    <hr>
                        <?php } ?>
                </div>

I’ll put a piece of my code in there for you to see.

Answer 1

I use this script:

echo date("d-m-Y H:m:s",strtotime("2016-10-04 15:51:16"));

You can also turn this into a function:

function formataData($data){
    return date("d-m-Y H:m:s",strtotime(data));
}

echo formataData("2016-10-04 15:51:16");

Answer 2

With PHP:

//para testar
//$dateObj = new DateTime('2016-10-04 15:51:16');
$date    = $linharesp['data'];
$dateObj = new DateTime($date);
echo  $dateObj->format('d-m-Y H:i:s');

With SQL (note that I’m only doing select with the date column,).

SELECT DATE_FORMAT(colunaComData, '%d-%m-%Y %H:%i:%s') as apelido_que_quiser from suaTabela;

Answer 3

I always use PHP’s Datetime class.

DateTime::createFromFormat('Y-m-d H:m:s','2016-10-04 15:51:16')->format('d-m-Y H:m:s');