in reply to Re^2: Create a search by date in a unix timestamp data
in thread Create a search by date in a unix timestamp data

... I hope you understand

I think you want 

select
 FROM_UNIXTIME('%Y:%M:%D'),
 FROM_UNIXTIME('%h:%i:%s'),
 other, fields, you want
... rest of query

[download]
See http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime

Replies are listed 'Best First'.
Re^4: Create a search by date in a unix timestamp data
by maicon1121 (Initiate) on May 11, 2012 at 13:52 UTC
    Ok, now, look at my code, in the first post. In my select, i save the "timestamp" value in the var "$timestamp", and list it in "while", my question is, how to print this twice, 1 with date, and 1 with hour?
[reply]

      Ok, now, look at my code, in the first post. In my select, i save the "timestamp" value in the var "$timestamp", and list it in "while", my question is, how to print this twice, 1 with date, and 1 with hour?

      Really? If that is your question, you write 

      my $timestamp = '2012-05-11 06:55:36';

      [download]

      How do I print $timestamp twice?

      The answer is  print $timestamp, $timestamp;

      Does that answer your question?

[reply]
[d/l]
[select]
Re^4: Create a search by date in a unix timestamp data
by maicon1121 (Initiate) on May 11, 2012 at 14:18 UTC

    Ok man, we are getting closer.

    Now, my while is showing hour 2 times.

    I'll send my actual code, thanks.

    Here: http://www.flickr.com/photos/maicon1121/7176525646/in/photostream, a print of my script page, for u can understand.

    #!C:\wamp\bin\perl\bin\perl.exe

BEGIN { use CGI::Carp "fatalsToBrowser"; }
use CGI qw(param);
use DBI;
use warnings;
use strict;
my $porpagina = param('porpagina');

print "Content-type: text/html \n\n";

print <<END

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1 Transitional//EN" "http:/
+/www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Netdeep Secure</title>

</head>

END
;

# Criei um objeto CGI 

my $cgi = CGI->new;

# Carreguei o post numa variável e coloquei no sql  das 2 consultas
my $var = $cgi->param("nome");
if (!$porpagina) {
$porpagina = 150;   
}

# Conexão com o BD 
my $dbh = DBI->connect('DBI:mysql:messenger;host=localhost', 'root', '
+'
               ) || die "Could not connect to database: $DBI::errstr";

# Faz uma segunda consulta no banco para obter os dados do registro
my $sth = $dbh->prepare("SELECT FROM_UNIXTIME(timestamp, '%d/%m/%Y'), 
+FROM_UNIXTIME(timestamp, '%H:%i'), clientaddress, localid, remoteid, 
+CONVERT(eventdata USING utf8) FROM messages WHERE localid LIKE '%$var
+%' ORDER BY id DESC LIMIT $porpagina");
$sth->execute( );
$sth->{LongReadLen}=500000;
$sth->{LongTruncOk}=1;

print qq|

<script type="text/javascript">

function somente_numeros(porpagina){

    var campo = document.getElementById(porpagina).value;
    var divide = campo.split("");
    var quant = divide.length;
    var permitidas = new Array (1,2,3,4,5,6,7,8,9,0);
    var i, achou = 0;
    var text = '';
    
    for(i=0; i<10; i++){
        
        if(divide[quant - 1] == permitidas[i]){
            achou = 1;
            }
        }
    if(achou == 0){
        divide[quant - 1] = '';
        }
    
    for(i=0; i<quant; i++){
        text += divide[i];
        }
    document.getElementById(porpagina).value = text;
}

function enviar(){
   var campo = document.getElementById('nome').value;
   if( campo != ''){
    document.forms["pesquisa"].submit();
   }else{
  alert('O campo E-mail não pode ser vazio');   
}
}

</script>

|;

print <<END

<body>

<div style="width:960px; height:30px; background:#dbdbdb;">
<h2 style="padding-left:15px;">Pesquisa</h2>
</div>
<br>
<form action="script.cgi" method="post" name="pesquisa" id="pesquisa" 
+enctype="multipart/form-data">
E-mail:
<input name="nome" id="nome" type="text" size="30" style="margin-left:
+86px;" />
ex: contato\@empresa.com.br
<br><br>
Qtde. de registros:
<input type="text" name="porpagina" id="porpagina" size="5" style="mar
+gin-left:17px;" onkeyup="somente_numeros('porpagina')">
somente números
<br><br>
<input type="button" value="Buscar" onclick="enviar()" />
<input name="limpar" type="reset" value="Limpar" />
</form>

<br>

<div style="width:960px; height:30px; background:#dbdbdb;">
<h2 style="padding-left:15px;">Log</h2>
</div>

<table width="960">
  <tr>
    <th scope="col" width="10%">Data</th>
    <th scope="col" width="10%">Hora</th>
    <th scope="col" width="18%">Endereço IP</th>
    <th scope="col" width="21%">De</th>
    <th scope="col" width="23%">Para</th>
    <th scope="col" width="23%">Mensagem</th>
  </tr>
  
END
;

my $timestamp = '2012-05-11 06:55:36';

while ( my($timestamp, $timestamp, $clientaddress, $localid, $remoteid
+, $eventdata) = $sth->fetchrow_array() ) 
{
        print "
    <tr>
       <td align='center'>$timestamp</td>
       <td align='center'>$timestamp</td>
       <td align='center'>$clientaddress</td>
       <td align='center'>$localid</td>
       <td align='center'>$remoteid</td>
       <td align='center'>$eventdata</td>
    </tr>"."";
}

print <<END

</table>
<div style="width:960px; height:30px; background:#dbdbdb;">
</div>

END
;

print <<END

</body>
</html>

END
;

$sth->finish();

$dbh->disconnect; undef $dbh;

    [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom