Get dates with mysql

Question

Get dates with mysql

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have a problem, I want to recover dynamically what happened seven days ago, and then what happened six, five and so on until 1 day ago. The problem is that when I do between the date accumulates the data, it does not bring me what happened exactly 7 days if not that it brings me the accumulated of these 7 days.

for ($i=7; $i > 0; $i--)
    {
        $q = "select count(*) as cnt, 'promedio' as col
                    from oreData.events
                    where evt_listener <> $prop_id
                    and evt_date between now() - interval $i day and now()
                    union
                    select count(*) as cnt, evt_listener as col
                    from oreData.events
                    where evt_listener = $prop_id
                    and evt_date between now() - interval $i day and now()";
}

I tried to filter it with

and YEAR(evt_date) = YEAR(NOW())
and MONTH(evt_date) = MONTH(NOW())
and DAY(evt_date) = DAY(NOW() - INTERVAL $i DAY)

The problem is that if I am on day 1 of either month or the first day of the year the condition is no longer met, as I fix this

Thanks for the suggestions

mysql php

asked by Alberto Siurob 06.12.2017 в 02:55

source

2 answers

How to create in JS a variable equivalent to a static variable in PHP? How to compare images in MATLAB?

score 1 · Answer 1

The problem is that when I do between the date accumulates the data, it does not bring me what happened exactly 7 days if not that it brings me the accumulated of these 7 days.

You are using count(*) without grouping by date. Instead of:

select count(*) as cnt, 'promedio' as col
from oreData.events
where evt_listener <> $prop_id
and evt_date between now() - interval $i day and now()

It should be

SELECT evt_date, count(*) as cnt, 'promedio' as col
FROM oreData.events
WHERE evt_listener <> $prop_id
AND evt_date between (now() - INTERVAL $i DAY) AND now()
GROUP BY evt_date

(and that would return all rows, day by day, of what happened in the last $i days)

In any case, I am surprised that what comes after your UNION does not cause an error. It should be, then:

SELECT evt_date, count(*) as cnt, 'promedio' as col
FROM oreData.events
WHERE evt_listener <> $prop_id
AND evt_date between (now() - INTERVAL $i DAY) AND now()
GROUP BY evt_date

UNION ALL

SELECT evt_date, count(*) as cnt, $prop_id as col
FROM oreData.events
WHERE evt_listener = $prop_id
AND evt_date BETWEEN (now() - INTERVAL $i DAY) AND now()
GROUP BY evt_date

Another observation: if you are overwriting the query in the loop for , you will only execute the query of the last value of $i .

EDIT : correct syntax error (duplicate AND)

EDIT2 : I think it would be better to have an explicit syntax for the between

score 0 · Answer 2

Hi Alberto Siurob , prepare a small sql that does all the work, just execute it and will throw you all the events between the current date and 7 days ago:

SELECT *
FROM oreData.events
WHERE DATEDIFF(now(),evt_date) <=7 AND DATEDIFF(now(),evt_date) >0
ORDER BY evt_date DESC;

the first condition indicates the maximum number of days elapsed and the second the minimum number.

Greetings!.