Determining whether a month contains entries

Question

I have a list of timestamped logs and I'd like a query to return 12 booleans say whether a certain month contains any logs, for each month of the year (starting from January), i.e.:

(True, False, False, True, False ..., True)

I currently have the following query, which will show me all months containing data:

SELECT DISTINCT(EXTRACT(MONTH FROM logdate)) AS month
FROM mytable
WHERE EXTRACT(YEAR FROM logdate) = '2009'
ORDER BY month;

The output of which is a list of months containing data, e.g.:

(1, 2, 5, 6, 12)

I just can't work out the next step - any pointers would be appreciated.

I am using PostgreSQL v8.4.2. Thanks.

Answer 1

I'm not sure, if this is the most elegant solution, but it should work (tested on Postgres):

SELECT (CASE WHEN max(sub.jan)=1 THEN true ELSE false END) AS jan,
       (CASE WHEN max(sub.feb)=1 THEN true ELSE false END) AS feb,
       ...
FROM
(
    SELECT (CASE WHEN (EXTRACT(MONTH FROM logdate))=1 THEN 1 ELSE 0 END) AS jan,
    (CASE WHEN (EXTRACT(MONTH FROM logdate))=2 THEN 1 ELSE 0 END) AS feb,
    ...
    FROM mytable
    WHERE EXTRACT(YEAR FROM logdate) = '2009'
) as sub

Answer 2

Use generate_series():

SELECT
    CASE
        WHEN EXTRACT(MONTH FROM logdate) IS NULL THEN false
        ELSE true
    END,
    y AS month
FROM
    mytable RIGHT JOIN generate_series(1,12) AS x(y) ON EXTRACT(month FROM logdate) = y
WHERE 
    EXTRACT(YEAR FROM logdate) = '2009';

Answer 3

Move the WHERE to the JOIN condition:

SELECT DISTINCT
    CASE
        WHEN EXTRACT(MONTH FROM logdate) IS  NULL THEN false
        ELSE true
    END,
    y AS month
FROM
    mytable 
        RIGHT JOIN 
        (SELECT generate_series(1,12)) AS x(y) ON ((EXTRACT(MONTH FROM logdate)) = y AND EXTRACT(YEAR FROM logdate) = '2009')
ORDER BY 
    month ASC;

The year condition only apply for matching records.