http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=12167
M. Tompsett <[email protected]> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |ASSIGNED --- Comment #18 from M. Tompsett <[email protected]> --- (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. _______________________________________________ Koha-bugs mailing list [email protected] http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
