Show complete date range with NULL in PostgreSQL

Question

I'm trying to create this query to get all complete date on range and data with nulls if the date is not exist on the table

For example this is my tbl_example

Original data:

id | userid(str) | comment(str) | mydate(date)
1     0001          sample1      2019-06-20T16:00:00.000Z
2     0002          sample2      2019-06-21T16:00:00.000Z
3     0003          sample3      2019-06-24T16:00:00.000Z
4     0004          sample4      2019-06-25T16:00:00.000Z
5     0005          sample5      2019-06-26T16:00:00.000Z

Then:

select * from tbl_example where mydate between '2019-06-20' AND
DATE('2019-06-20') + interval '5 day')

how to output all the dates on range with possible null like this

Expected output:

id | userid(str) | comment(str) | mydate(date)
1     0001          sample1      2019-06-20T16:00:00.000Z
2     0002          sample2      2019-06-21T16:00:00.000Z
null  null          null         2019-06-22T16:00:00.000Z
null  null          null         2019-06-23T16:00:00.000Z
4     0003          sample3      2019-06-24T16:00:00.000Z
5     0004          sample4      2019-06-25T16:00:00.000Z

This is my sample test environment: http://www.sqlfiddle.com/#!17/f5285/2

The examples were not clear.What is your orginal data and what output do you want? — Shawn.X
– Shawn.X, Commented Jun 13, 2019 at 10:31
sorry i highlighted the orignal and the expected output .thank you shawn.X .. — Jakegarbo
– Jakegarbo, Commented Jun 13, 2019 at 10:36
Just see my answer, I think the result it returns is just what you want. :D — Shawn.X
– Shawn.X, Commented Jun 13, 2019 at 11:06

Shawn.X · Accepted Answer · 2019-06-13 11:39:56Z

2

OK, just see my SQL as below:

with all_dates as (
select generate_series(min(mydate),max(mydate),'1 day'::interval) as dates from tbl_example
)
,null_dates as (
select
    a.dates
from
    all_dates a
left join
    tbl_example t on a.dates = t.mydate
where
    t.mydate is null
)
select null as id, null as userid, null as comment, dates as mydate from null_dates
union
select * from tbl_example order by mydate;
 id | userid | comment |       mydate        
----+--------+---------+---------------------
  1 | 0001   | sample1 | 2019-06-20 16:00:00
  2 | 0002   | sample1 | 2019-06-21 16:00:00
    |        |         | 2019-06-22 16:00:00
    |        |         | 2019-06-23 16:00:00
  3 | 0003   | sample1 | 2019-06-24 16:00:00
  4 | 0004   | sample1 | 2019-06-25 16:00:00
  5 | 0005   | sample1 | 2019-06-26 16:00:00
(7 rows)

Or the generate_series clause you can just write the date arguments you want ,as below:

select generate_series('2019-06-20 16:00:00','2019-06-20 16:00:00'::timestamp + '5 days'::interval,'1 day'::interval) as dates

edited Jun 13, 2019 at 11:39

answered Jun 13, 2019 at 11:03

Shawn.X

1,3637 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jakegarbo Over a year ago

hmmm im so confused this "select generate_series('2019-06-20 16:00:00','2019-06-25 16:00:00','1 day'::interval) as dates" does not make the date range correct . but the data much closer to the expected output ..

Shawn.X Over a year ago

@JakeGarbo generate_series is to construct a consecutive dates period, and then you can left join your tbl_example data with it to get the missing dates, and then construct the null rows with the dates you get, finally union your origional tbl_example, you will get the result you want.

Jakegarbo Over a year ago

my sample used rage of 5days if I use generate_series(with the proper date range) i dont get the proper range so am confused. it shows upto 2019-06-27T00:00:00Z

Shawn.X Over a year ago

I have updated my answer above, you can write the second argument of generate_series with your_date + 5 days interval.

Shawn.X Over a year ago

And my sql is just an example to clarify the method to solve your problem, when you understand this method, you can improve it to meet your needs. :D

|

Deepstop · Accepted Answer · 2019-06-14 00:01:05Z

1

SELECT id, userid, "comment", d.mydate
    FROM generate_series('2019-06-20'::date, '2019-06-25'::date, INTERVAL '1 day') d (mydate)
    LEFT JOIN tbl_example ON d.mydate = tbl_example.mydate

Result

edited Jun 14, 2019 at 0:01

answered Jun 13, 2019 at 10:34

Deepstop

3,8372 gold badges12 silver badges23 bronze badges

3 Comments

Jakegarbo Over a year ago

im getting error : syntax error at or near "userid" Position: 35 im using postgres v9.6 as test

Jakegarbo Over a year ago

the result has no 2019-06-22 and 2019-06-23 and it not get the proper range of 2019-06-20 to 2019-06-25 result .. it exceed to 2019-06-27

Deepstop Over a year ago

OK. I still did not understand your requirements properly. Now I think I do. You see the start and end dates in the generate_series function. I tested it and it the output is the same as you asked for.

Collectives™ on Stack Overflow

Show complete date range with NULL in PostgreSQL

2 Answers 2

7 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related