Database date returning 12/31/1969

Question

I have in my script a call to date and to print for me brings me a date of 12/31/1969, and not the date that is in the database.

This is my Code:

@$pag = "$_GET[pag]";
        if($pag >= '1'){
         $pag = $pag;
        }else{
         $pag= '1';
        }

        $maximo = '5'; //RESULTADOS POR PÁGINA
        $inicio = ($pag * $maximo) - $maximo;


  $topico = $_GET['cat'];

  $noticias = mysqli_query($conexao,"SELECT  id, thumb, titulo, texto, categoria, 'data', autor, valor_real, valor_pagseguro, visitas FROM lp_post WHERE 
  categoria = '$topico' ORDER BY data DESC LIMIT $inicio,$maximo")or die (mysqli_error($conexao));

            if(@mysqli_num_rows ($noticias) <= '0'){
             echo "Nenhuma mensagem encontrada no momento"; 
                }else{

                    $numero = '0';

                  while($res_noticias = mysqli_fetch_array($noticias)){
                    $id = $res_noticias[0];
                    $thumb = $res_noticias[1];
                    $titulo = $res_noticias[2];
                    $texto = $res_noticias[3];
                    $categoria = $res_noticias[4];
                    $data = $res_noticias[5];
                    $autor = $res_noticias[6];
                    $valor_real = $res_noticias[7];
                    $valor_pagseguro = $res_noticias[8];
                    $visitas = $res_noticias[9];
                    $numero ++;




?>
<div class="categoria">
<a href="index.php?topicos=nav/single&amp;topico=<?php echo $id; ?>">
    <h1><?php echo $titulo; ?></h1>

    <span class="info">Data:<?php echo date("d/m/Y - H:m", strtotime($data)); ?> | Autor: <?php echo $autor ?> | Categoria: <?php echo $categoria ?> | Visitas:<?php echo $visitas ?> </span>

      <img src="uploads/<?php echo $categoria ?>/<?php echo $thumb ?>"  class="alinleft" alt="<?php echo $titulo ?>" width="100" height=""/>

    <p class="categoria_p"><?php echo strip_tags(trim(str_truncate($texto, 175, $rep=TRUNC_BEFORE_LENGHT))); ?></p>
  </a>  
</div> 

<?php
    }
 }
 ?>
    <div class="paginator">
<?php

        //USE A MESMA SQL QUE QUE USOU PARA RECUPERAR OS RESULTADOS
        //SE TIVER A PROPRIEDADE WHERE USE A MESMA TAMBÉM
        $sql_res = mysqli_query($conexao,"SELECT * FROM lp_post WHERE categoria = '$topico'");
        $total = mysqli_num_rows($sql_res);

        $paginas = ceil($total/$maximo);
        $links = '5'; //QUANTIDADE DE LINKS NO PAGINATOR

        echo "<a href=\"index.php?topicos=nav/categoria&amp;cat=$categoria&amp;pag=1\">Primeira Página</a>&nbsp;&nbsp;&nbsp;";


        for ($i = $pag-$links; $i <= $pag-1; $i++){
        if ($i <= 0){
        }else{
        echo"<a href=\"index.php?topicos=nav/categoria&amp;cat=$categoria&amp;pag=$i\">$i</a>&nbsp;&nbsp;&nbsp;";
        }
        }echo "$pag &nbsp;&nbsp;&nbsp;";

        for($i = $pag +1; $i <= $pag+$links; $i++){
        if($i > $paginas){
        }else{
        echo "<a href=\"index.php?topicos=nav/categoria&amp;cat=$categoria&amp;pag=$i\">$i</a>&nbsp;&nbsp;&nbsp;";
        }
        }
        echo "<a href=\"index.php?topicos=nav/categoria&amp;cat=$categoria&amp;pag=$paginas\">Última página</a>&nbsp;&nbsp;&nbsp;";
?>

The error given is the date of 12/31/1696 and not the date of the database.

The database configuration is timestamp.

Answer 1

Your problem is that you are using simple quotes for the date field 'data' when you should use the crase

`data`

When the resultset returns to php, it sends the string data making the function strtotime($data) return the first timestamp day.

Fix your query to:

$noticias = mysqli_query($conexao, 
                "SELECT  id, thumb, titulo, 
                         texto, categoria, `data`, 
                         autor, valor_real, valor_pagseguro, 
                         visitas 
                   FROM lp_post 
                  WHERE categoria = '$topico' 
                  ORDER BY data DESC LIMIT $inicio,$maximo") 
              or die (mysqli_error($conexao));

Answer 2

Since I answered initially in the comment, I will post here.

To ensure that you are speaking of a database symbol, such as a column, for example and not to be confused with SQL syntax, you need to use backtick (crase). Your code is using simple quotes which is the delimiter of a text. So what is being used there is the text 'data'. When trying to interpret this text as date, it returns an invalid date which is a value prior to the 01/01/1970, the beginning of timestamp.

The solution is to fix the syntax.

SELECT id, thumb, titulo, texto, categoria, `data`, autor, valor_real,
            valor_pagseguro, visitas FROM lp_post

Or how it works too, after all data does not get confused with SQL in this case:

SELECT id, thumb, titulo, texto, categoria, data, autor, valor_real,
            valor_pagseguro, visitas FROM lp_post

I put in the Github for future reference.