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 :
SELECT
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.id = 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 
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 
@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) ...
nvm there is an issue with my sql query, i'll come back when it is ok
