1

I have a database table that contains the following data:

ID | Date       | Bla
1  | 2013-05-01 | 1
2  | 2013-05-02 | 2
3  | 2013-05-03 | 3
4  | 2013-05-05 | 4

Note that there is a date missing: 2014-05-04. How should I alter the following query:

SELECT * 
FROM table 
where DATE >= '2013-05-01' AND DATE <= '2013-05-05'

So that I would end up with the following output:

ID   | Date       | Bla
1    | 2013-05-01 | 1
2    | 2013-05-02 | 2
3    | 2013-05-03 | 3
null | 2013-05-04 | null
4    | 2013-05-05 | 4

Is this possible?

Improve this question

3 Answers 3

Reset to default
3

You can join with a generate_series output:

select
    '2013-05-01'::date + g.o AS "date with offset"
from
    generate_series(0, 30) AS g(o)

Output:

"2013-05-01"
"2013-05-02"
"2013-05-03"
...
"2013-05-29"
"2013-05-30"
"2013-05-31"

Or... an easier method after defining a new stored procedure :)

CREATE OR REPLACE FUNCTION generate_series(date, date) RETURNS
SETOF date AS $$
SELECT $1 + g.s
FROM generate_series(0, ($2 - $1)) AS g(s);
$$ LANGUAGE SQL IMMUTABLE;

Just call it like this:

SELECT * FROM generate_series(start_date, end_date);
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

How can this be used if you only have a start and end date?
Use something like this: generate_series(0, (end_date - start_date)::integer)`
As long as you're on Postgres 8.4 or higher, you don't need to define a custom generate_series function, as there's a native generate_series(from_timestamp, to_timestamp, step_interval) variant (just use '1 day'::interval as the step in this case).
Sorry for the edit but that long list was really annoying me :)
@ClodoaldoNeto: fair enough :)
|
3 
select *
from 
    (
        select generate_series(
            '2013-05-01'::date, '2013-05-05', '1 day'
            )::date
    ) s("date")
    left join
    t using ("date")

Replace both "date" with the actual column name.

Improve this answer

1 Comment

+1 It's the best code. But some explanation and maybe a link to the manual for generate_series() would be nice.
2

You need to outer join your table against a "list of dates":

with all_dates (some_date) as (
    select date '2013-05-01' + i 
    from generate_series(0, 10) i  -- adjust here to the range of dates you need.
) 
select t.id, 
       ad.some_date, -- you need to take the actual date from generated ones
       t.bla
from all_dates ad
  left join the_table t on ad.some_date = t.date
where ad.some_date between date '2013-05-01' and date '2013-05-05';

Btw: date is a horrible name for a column. Apart from the fact that it is a reserved word it also tells nothing about what kind of "date" that is.

Improve this answer

5 Comments

As shown in Clodoaldo Neto's answer, generate_series can create a series of dates natively, simplifying the CTE somewhat, and making it easier to remove the WHERE clause completely in favour of a properly chosen date range in the CTE. Nicely laid out answer otherwise though.
Oh, and I would imagine DATE is an anonymised/example column name, since the table in the query is referred to as TABLE, which is an unlikely name for a table :)
@IMSoP a_horse sure knows about the date overload of generate_series. He is probably considering if the OP uses an older postgresql version with only the integer overload of generate_series
@ClodoaldoNeto: to be honest: I always forget about the "date" version of generate_series() ;)
Regarding versioning, it's available since 8.4, which came out 4 years ago and is now the oldest "fully supported" version, so it's probably safe to assume it will be there now. :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.