Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2014
    Posts
    1

    Unanswered: Return row for a range of dates.

    I am attempting to query my DB for a date range using BETWEEN 'date_1' AND 'date_2' BUT i want the query to return the date if the sum(column_name) is 0 (no rows returned). I have tried multiple ways using COALESCE and sub selects but to no avail.

    Query basically looks like:

    SELECT sum(users), date FROM table WHERE date BETWEEN 'date_1' AND 'date_2'.

    And should return rows for each date regardless if there is data, for the dates between date_1 and date_2.

    Thanks to all in advance.

  2. #2
    Join Date
    Jul 2015
    Posts
    7

    788

    you can try with generate_series function in posgresql.
    so it will generate first dates; and the dates which are not available will be zero;

    select coalesce(sum(yt.users), 0),
    t.dates
    from generate_series('date_1'::timestamp, 'date_2'::timestamp, '1 day'::interval) as t(dates)
    left join your_table as yt on yt.date = t.dates
    group by t.dates;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •