Page 1 of 1

mysql query help

Posted: Fri Aug 01, 2008 9:17 am
by ddragas
Hi all

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)
 

Re: mysql query help

Posted: Fri Aug 01, 2008 10:34 am
by jaoudestudios
Use substr, you know how many characters at each end of the string so just substract 1 character from the front and 1 from the end.

Re: mysql query help

Posted: Sun Aug 03, 2008 2:04 pm
by pkbruker
Working with dates/time sepparated into hours, minutes, day and so on, can be a mighty nightmare. PHP's time() function returns the time as seconds from Jan 1st 1970. This means you can do normal mathematical calculations with it, such as

< and > to find out if a date is before/after an other date.
Subtract one date from an other to get seconds (and minutes, hours) between the dates
and more...

Your query looks, well, rather complicated. Maybe by using these kind of dates, it could be made a lot simpler.

Re: mysql query help

Posted: Sun Aug 03, 2008 2:58 pm
by ddragas
thank you for reply,

I know for time function, but this is not occasion for it to use it here.

I've got to get rid of other character but one I'm looking for