Menu
When I apply a date range to my query, is there anyway to display the dates used in the date range even if there is no data at those dates?
Suppose I use,
... where date between '1/12/2010' and '31/12/2010' order by date
What I want in my result is to show sum of all amount column until 1/12/2010 on that day even if there is no data for that date and also same for 31/12/2010.
420
In the calendar, click the desired start date, then click the end date. The selected days are highlighted. OR. Enter start and end dates in the Date Range fields.
Use the PostgreSQL AGE() function to retrieve the interval between two timestamps or dates. This function takes two arguments: the first is the end date and the second is the start date.
The date format for the date data type in PostgreSQL is yyyy-mm-dd . This is the format used for both storing data and for inserting data.
Range types are a unique feature of PostgreSQL, managing two dimensions of data in a single column, and allowing advanced processing. The main example is the daterange data type, which stores as a single value a lower and an upper bound of the range as a single value.
Join with generate_series() to fill in the gaps.
Example:
CREATE TEMP TABLE foo AS SELECT CURRENT_DATE AS today;
SELECT
COUNT(foo.*),
generate_series::date
FROM
foo
RIGHT JOIN generate_series('2010-12-18', '2010-12-25', interval '1 day') ON generate_series = today
GROUP BY
generate_series;
Result:
0,'2010-12-18'
0,'2010-12-19'
1,'2010-12-20'
0,'2010-12-21'
0,'2010-12-22'
0,'2010-12-23'
0,'2010-12-24'
0,'2010-12-25'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
