[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

M. Tompsett <mtompset at hotmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|Signed Off                  |ASSIGNED

--- Comment #18 from M. Tompsett <mtompset at hotmail.com> ---
(In reply to Ludwin Hernández Vásquez from comment #17)
> 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:
[SNIP]
>      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.

[SNIP and REORDER]
> functions to substitute or remove
> *CURRENT_DATE() equivalent in postgresql current_date
1) current_date and CURRENT_DATE are equivalent in postgresql, as far as I can
tell (http://www.postgresql.org/docs/8.1/static/functions-datetime.html -- See
Table 9-26), though perhaps some SQLs may be case sensitive. It functions in
postgresql just fine, as far as I can tell. Though, it does barf on
CURRENT_DATE() with the ()'s, so I'll remove that.


> *OR expirationdate = '00-00-0000' not proper for postgresql

Okay, after an hour or two of trying to get postgresql installed and trying to
mimick this type of query, I see the problem.

This would potentially need another patch to actually correct any historical
mysql news by updating expirationdate=NULL where expirationdate='0000-00-00' in
order to safely remove this. Though, I am surprised it functioned with
'00-00-0000'. Anyways, I guess I'll add a follow up patch.

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