Mysql select query can extract id 6digit from a field if is a url this field of type

Databases
42

I modified date with LEFT and seems working … thanks for all your help…

left(bcc_logs.date, 10)

SELECT Yesterday.SystemDate
     , idList.ID
     , COUNT(left(bcc_logs.date, 10))
  FROM (SELECT CAST(NOW() - INTERVAL 1 DAY AS DATE) AS SystemDate) Yesterday
 INNER JOIN (SELECT DISTINCT SUBSTRING(page, INSTR(page, 'car_id=')+7, 6) ID 
               FROM bcc_logs) idList ON 1=1 AND 
                                        idList.ID REGEXP '[[:digit:]]+' AND
                                        CONVERT(idList.ID, UNSIGNED INTEGER) BETWEEN 275001 AND 999999
  LEFT JOIN bcc_logs ON (left(bcc_logs.date, 10) = Yesterday.SystemDate AND 
			 isbot = 0 AND
			 idList.ID = SUBSTRING(page,INSTR(page,'car_id=')+7,6))
GROUP BY Yesterday.SystemDate
       , idList.ID
43

the operative word here is “seems”

please, do not use string functions on DATE columns

also, COUNT(left(bcc_logs.date, 10)) will count ~exactly~ the same number of values as COUNT(bcc_logs.date)

finally, do not use a reserved word (DATE) to name a column

thank you

1 Like
44

Please note only when I insert Left worked since dates are in date time format. …both in sqlfiddle and in my sandbox

You have any other way round the date in other words remove time?

45

yes… the DATE() function

1 Like
46

ok thanks for your help

47

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.