[Koha-bugs] [Bug 12167] datetime type casting issue regarding bug 7567

[bugzilla-daemon at bugs.koha-community.org]

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12167

Ludwin Hernández Vásquez <alduvi11 at gmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |alduvi11 at gmail.com

--- Comment #17 from Ludwin Hernández Vásquez <alduvi11 at gmail.com> ---
It seems to be that the function your working with not avoid mysqlism.

I propose a bit of code that work with mysql and postgresql:

sub GetNewsToDisplay {
    my ($lang,$branch) = @_;
    my $dbh = C4::Context->dbh;
    # SELECT *,DATE_FORMAT(timestamp, '%d/%m/%Y') AS newdate
    my $query = q{
     SELECT *,opac_news.timestamp AS newdate
     FROM   opac_news
     WHERE   (
        expirationdate >= current_date
        OR    expirationdate IS NULL
        #OR    expirationdate = '00-00-0000' not proper for postgresql          
     )
     AND  CAST(opac_news.timestamp AS DATE) <= current_date
     AND   (lang = '' OR lang = ?)
     AND   (branchcode IS NULL OR branchcode = ?)
     ORDER BY number
    }; # expirationdate field is NOT in ISO format?
       # timestamp has HH:mm:ss, CURRENT_DATE generates 00:00:00
       #           by adding 1, that captures today correctly.
    my $sth = $dbh->prepare($query);
    $lang = $lang // q{};
    $sth->execute($lang,$branch);
    my @results;
    while ( my $row = $sth->fetchrow_hashref ){
        $row->{newdate} = format_date($row->{newdate});
        push @results, $row;
    }
    return \@results;
}

functions to substitute or remove
*OR expirationdate = '00-00-0000' not proper for postgresql           
*CURRENT_DATE() equivalent in postgresql current_date

kind regards! 

Ludwin Hernández Vásquez Universidad de El Salvador

-- 
You are receiving this mail because:
You are watching all bug changes.