I've got problem with mysql query:
in field name hour is value '-11--12--13--14--15-'
there is possibility of values from 0 - 23 with character '-' (minus) at both sides of hour
how could I get only value '13' and "trim" all other characters ?
regex ?
string replace?
here is complete query
fields translation:
sat = hour
godina = year
mjesec = month
dan = month day
tjedniDan = day in week
period_od = period from
period_do = period to
Code: Select all
SELECT cronjob.*
FROM cronjob
WHERE
(tjedniDan = DAYOFWEEK(CURDATE()) OR tjedniDan = '0')
AND (
CAST(
CONCAT(
IF(godina = '*' OR godina='', DATE_FORMAT(NOW(), '%Y'), godina),
IF(mjesec = '*', DATE_FORMAT(NOW(), '%m'), mjesec ),
IF(dan = '*', DATE_FORMAT(NOW(), '%d'), dan ),
IF(sat = '*',
CASE
WHEN (TIME_FORMAT(NOW(), '%i') BETWEEN 46 AND 59) AND (cast(minuta AS UNSIGNED) BETWEEN 0 AND 14) THEN IF(TIME_FORMAT(NOW(), '%H')+1 < 10, CONCAT('0', TIME_FORMAT(NOW(), '%H')+1), TIME_FORMAT(NOW(), '%H')+1)
WHEN (TIME_FORMAT(NOW(), '%i') BETWEEN 0 AND 14) AND (cast(minuta AS UNSIGNED) BETWEEN 46 AND 59) THEN IF(TIME_FORMAT(NOW(), '%H')-1 < 10, CONCAT('0', TIME_FORMAT(NOW(), '%H')-1), TIME_FORMAT(NOW(), '%H')-1)
ELSE TIME_FORMAT(NOW(), '%H')
END
, sat LIKE CONCAT('%,', DATE_FORMAT(NOW(), '%H'), ',%')),
IF(minuta = '*', TIME_FORMAT(NOW(), '%i'), minuta)
) AS UNSIGNED
) BETWEEN 200808011216 AND 200808011244)
AND IF(period_od <> '' , (NOW() >= period_od), id > 0)
AND IF(period_do <> '', (NOW() <= period_do), id > 0)