denzil_cactus has asked for the wisdom of the Perl Monks concerning the following question:

HI Monks!! Please help me here. 
my $query = "SELECT DISTINCT ID,unix_timestamp(tUpdate) as t,unix_time
+stamp(tEnter) as p from ini_Rate where tUpdate > tEnter limit 5 ";

    $sth = $dbh->prepare($query);
    $sth->execute();

    while ( my $row = $sth->fetchrow_hashref)
    {
        
    
            
            my $cRequest = ""; my $cTratupdated = "";

            if($row->{t} > $row->{p}) 
            { 
                    {
                $cRequest = "U";  # Update
                $ctupdate = $row->{tUpdate};
                
            }
            else
            {
                $cRequest = "N";  # New
            }

[download]
In the above code, tUpdate and tEnter are dates and I have converted them into unix timstamp for comparing but now I need to convert back to yyyy-mm-dd hh:mm:ss format i.e mysql datetime field format. Can anyone advise me how to convert it?

Replies are listed 'Best First'.
Re: Converting dates
by ejaincom (Novice) on Aug 12, 2008 at 08:46 UTC
    Easy,
    Just use two other columns in the query itself,
    ie
    my $query = "SELECT DISTINCT ID,unix_timestamp(tUpdate) as t,unix_time +stamp(tEnter) as p,tUpdate, tEnter from ini_Rate where tUpdate > tEnter limit 5 ";

    Thanks
    Abhi
[reply]
      Thanks Abhi!! It is the best solution. :-)
[reply]
Re: Converting dates
by Corion (Patriarch) on Aug 12, 2008 at 06:59 UTC

    See POSIX::strftime:

    print strftime '%Y-%m-%d %H:%M:%S', gmtime($timestamp);

    [download]
[reply]
[d/l]
      Thanks it works but now the problem is 
      
use POSIX qw(strftime);



$str = "1164414128";

print strftime '%Y-%m-%d %h:%m:%S', gmtime($str);

      [download]
      I am getting the print 2006-11-25 00:22:08 but when I queried from the database it was 
      
SELECT ini_Rate.tUpdate from ini_Rate where ini_Rate.tUpdate > ini_Rat
+e.tEnter limit 1;
+---------------------+
| tUpdate             |
+---------------------+
| 2006-11-25 05:52:08 |
+---------------------+


SELECT unix_timestamp(ini_Rate.tUpdate) from ini_Rate where ini_Rate.t
+Update > ini_Rate.tEnter limit 1;
+-----------------------------------------+
| unix_timestamp(ini_Rate.tUpdate)        |
+-----------------------------------------+
|                              1164414128 |
+-----------------------------------------+

      [download]
      So I need to get 2006-11-25 05:52:08 but its printing 2006-11-25 00:22:08 suggest me
[reply]
[d/l]
[select]
        The %h in your strftime call should be %H, because otherwise it will be a three letter month string. The difference in time is probably a timezone difference. Here it is five and a halve hour. By using localtime you should get the right time. This is hard to test, because localtime is different for me.
        Peter Stuifzand
[reply]
Re: Converting dates
by dHarry (Abbot) on Aug 12, 2008 at 07:04 UTC

    The are many ways as usual. One example:

    use Time::localtime;
$tm = localtime($time); 
printf("Dateline: %02d:%02d:%02d-%04d/%02d/%02d\n",
$tm->hour, $tm->min, $tm->sec, $tm->year+1900,
$tm->mon+1, $tm->mday);

    [download]

    This gives the time in your local timezone. If you rather have GMT use the gmtime function.

    Bless the Perl Cookbook. If you perform a Super Search you will find many examples. Also on CPAN there are many modules to work with date/times.

    Hope this helps.

[reply]
[d/l]
[select]
Re: Converting dates
by Anonymous Monk on Aug 12, 2008 at 07:32 UTC
    unix_timestamp(tUpdate) thats mysql function, so use another, from_unixtime
[reply]
Re: Converting dates
by chrism01 (Friar) on Aug 13, 2008 at 05:19 UTC
    I'm worried that your SQL is only SELECTing rows where tUpdate > tEnter, and yet you are doing the same test again in Perl.
[reply]

Back to Seekers of Perl Wisdom