Last 12 months, group by week

Question

Hi,

I have a table with a column REGDATE, a registration date (YYYY-MM-DD HH:MM:SS). I would like to show an histogram (ExtJS) in order to understand in which period of the years users are signing up. I would like to do this for the past twelve months with respect to the current date and to group dates by week.

Any hints?

Answer 1

Hint: (SQL)

SELECT CONVERT (VARCHAR(7), REGDATE, 120) AS [RegistrationMonth]
FROM ...
GROUP BY CONVERT (VARCHAR(7), REGDATE, 120)
ORDER BY CONVERT (VARCHAR(7), REGDATE, 120)

Answer 2

in MySQL:

SELECT COUNT(*), DATE_FORMAT(regdate, "%X%V") AS regweek FROM table GROUP BY regweek;

or

SELECT COUNT(*), YEARWEEK(NOW(), 2) as regweek FROM table GROUP BY regweek;

in PostgreSQL:

SELECT COUNT(*), EXTRACT(YEAR FROM regdate) ||  EXTRACT(WEEK FROM regdate) AS regweek FROM table GROUP BY regweek;