Rolling time period on the x-axis of a histogram

hi everyone,
i would like to display the result of a query sorting the selected elements on a rolling month in a histogram. this is my query :

    type.name_type as type_name,
    extract(day from event.creation_date) as days,
    count(0) as number
FROM event, type, event_type
WHERE = event_type.event_id
    AND type.type_id = event_type.type_id
    AND event.creation_date BETWEEN NOW() - INTERVAL '1 MONTH' AND NOW()
GROUP BY days, type_name
ORDER BY min(event.creation_date) asc;

for example we are the 6th today, I would like the x-axis to go from 7, 8, 9, ... , 30/31, 1,2 ... down to 6.
anyone can help ?
have a nice day :slight_smile:

Hi @armandviolle
So what is the problem? I don't think I understand what you're having problems with.

that's because i didn't explained it ^^ my bad
with my example, i want it displayed from the 7 to the 6 (as i explained)
but it is displayed from the 1 to the 30/31... when i visualize the result in a table it is well ordered from the 7 to the 6 though

@armandviolle Histograms and Timeseries are ordered. You would have to change the "X-axis style" to Ordinal instead.

ok i changed the x-asis style to ordinal

it is getting Closer but still the x-asis is not "ordered" as i would like, let me show you

this is what I get for a 1 month interval ; this results is good because the days are ordered according to the rolling month

but when i have a larger time interval (1 year for example) :

the days aren't ordered from the 7 to the 6 in a chronological way...

is there a Way to fix that ?

@armandviolle Try ORDER BY days instead. Show your data as a table, which helps you understand the current sorting better.

ok this is what i get with the ORDER BY days:

this is my starting point ; i'm not happy with this result because the x-axis is from the 1st to the 31st whereas i want it to be a rolling month so from the 7th to the 6th (based on current date).

when visualizing returned data with tables, the result of my initial request is well ordered and exactly what i want. that's why i think the problem comes from the histogram settings...

@armandviolle Okay, then try ORDER BY event.creation_date
Otherwise just return the full date instead of a number.

i get the same issue i described in my message 2h ago ://

and i can't just return the date because i need to group by the day's numbers :confused:

@armandviolle Okay, then convert it to a string instead (remember to prepend zero to numbers less than 10).

@flamber i still have the same issue (i tested with both order by) ... :confused:

nvm there is an issue with my sql query, i'll come back when it is ok