CODEWITHSUNDEEP
vertica
Swathi8038
Swathi8038

Reputation: 96

Select Distinct Records with overlapping conditions

I have a query to select count of dates between '2017-01-01' and '2017-03-01' is there anyway to calculate separately for '2017-01-01','2017-02-01' and '2017-03-01' without using case statements.

Something like using group by

Upvotes: 0

Views: 159

Answers (2)

sKwa
sKwa

Reputation: 889

You need function date_trunc. It will truncate your dates/timestamps to month.

daniel=> select date_trunc('month', '2017-01-31'::date);
     date_trunc  
---------------------
 2017-01-01 00:00:00
(1 row)

daniel=> select date_trunc('month', '2017-03-17'::date);
     date_trunc  
---------------------
 2017-03-01 00:00:00
(1 row)

i.e. query will look something like:

SELECT
    date_trunc('month', date) as 'month',
    count(*)                  as 'count'
FROM
    table
WHERE
    date_trunc('month', date) >= '2017-01-01'
        AND
    date_trunc('month', date) <= '2017-03-01'
GROUP BY
    date_trunc('month', date);

If you need to generate time series, use in row_number() over () and any table with enough rows, for example system_tables:

daniel=> select '2017-01-01'::DATE + cast(row_number() over () - 1 as interval month) from system_tables limit 5;
      ?column?
---------------------
 2017-01-01 00:00:00
 2017-02-01 00:00:00
 2017-03-01 00:00:00
 2017-04-01 00:00:00
 2017-05-01 00:00:00
(5 rows)

Also you can try TIMESERIES with gap filling:

SELECT ts AS 'date' FROM (
    SELECT '2017-01-01'::TIMESTAMP AS tm
        UNION
    SELECT '2017-01-03'::TIMESTAMP AS tm) AS t
TIMESERIES ts AS '8 hours' OVER (ORDER BY tm);

daniel=> \e
        date
---------------------
 2017-01-01 00:00:00
 2017-01-01 08:00:00
 2017-01-01 16:00:00
 2017-01-02 00:00:00
 2017-01-02 08:00:00
 2017-01-02 16:00:00
 2017-01-03 00:00:00
(7 rows)

Now, lets combine all together and will count days per month with date_trunc:

SELECT
    date_trunc('month', T.date) AS 'MONTH',
    count(*)                    AS 'COUNT'
FROM (
    SELECT ts::DATE AS 'date' FROM (
        SELECT '2017-01-01'::TIMESTAMP AS tm
            UNION ALL
        SELECT '2017-03-01'::TIMESTAMP AS tm
    ) AS S
    TIMESERIES ts AS '8 hours' OVER (ORDER BY tm)
) AS T
GROUP BY 1
ORDER BY 1;

daniel=> \e
        MONTH        | COUNT
---------------------+-------
 2017-01-01 00:00:00 |    93
 2017-02-01 00:00:00 |    84
 2017-03-01 00:00:00 |     1
(3 rows)

Upvotes: 1

Elbek
Elbek

Reputation: 646

SELECT date, count(*) FROM table WHERE date>='2017-01-01' AND date<='2017-03-01' GROUP BY date;

Or you can change dates inclusive or exclusive

Upvotes: 0

Related Questions