Problem
Using standard mysql functions is there a way to write a query that will return a list of days between two dates.
For example, given the dates 2009-01-01 and 2009-01-13, it would return a one-column table with the following values:
2009-01-01
2009-01-02
2009-01-03
2009-01-04
2009-01-05
2009-01-06
2009-01-07
2009-01-08
2009-01-09
2009-01-10
2009-01-11
2009-01-12
2009-01-13
Edit: It looks that I was not clear. I’d want to CREATE this list. I have values in the database (by datetime), but I want to aggregate them using a left outer join to a list of dates, as seen above (I am expecting null from the right side of some of this join for some days and will handle this).
Asked by Gilgad
Solution #1
I’d use this stored procedure to construct the intervals you require in the temp table time intervals, then JOIN and aggregate your data table with the temp table time intervals.
The procedure can generate intervals of any of the types provided in the protocol:
call make_intervals('2009-01-01 00:00:00','2009-01-10 00:00:00',1,'DAY')
.
select * from time_intervals
.
interval_start interval_end
------------------- -------------------
2009-01-01 00:00:00 2009-01-01 23:59:59
2009-01-02 00:00:00 2009-01-02 23:59:59
2009-01-03 00:00:00 2009-01-03 23:59:59
2009-01-04 00:00:00 2009-01-04 23:59:59
2009-01-05 00:00:00 2009-01-05 23:59:59
2009-01-06 00:00:00 2009-01-06 23:59:59
2009-01-07 00:00:00 2009-01-07 23:59:59
2009-01-08 00:00:00 2009-01-08 23:59:59
2009-01-09 00:00:00 2009-01-09 23:59:59
.
call make_intervals('2009-01-01 00:00:00','2009-01-01 02:00:00',10,'MINUTE')
.
select * from time_intervals
.
interval_start interval_end
------------------- -------------------
2009-01-01 00:00:00 2009-01-01 00:09:59
2009-01-01 00:10:00 2009-01-01 00:19:59
2009-01-01 00:20:00 2009-01-01 00:29:59
2009-01-01 00:30:00 2009-01-01 00:39:59
2009-01-01 00:40:00 2009-01-01 00:49:59
2009-01-01 00:50:00 2009-01-01 00:59:59
2009-01-01 01:00:00 2009-01-01 01:09:59
2009-01-01 01:10:00 2009-01-01 01:19:59
2009-01-01 01:20:00 2009-01-01 01:29:59
2009-01-01 01:30:00 2009-01-01 01:39:59
2009-01-01 01:40:00 2009-01-01 01:49:59
2009-01-01 01:50:00 2009-01-01 01:59:59
.
I specified an interval_start and interval_end so you can aggregate the
data timestamps with a "between interval_start and interval_end" type of JOIN.
.
Code for the proc:
.
-- drop procedure make_intervals
.
CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
-- Author: Ron Savage
-- Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
declare thisDate timestamp;
declare nextDate timestamp;
set thisDate = startdate;
-- *************************************************************************
-- Drop / create the temp table
-- *************************************************************************
drop temporary table if exists time_intervals;
create temporary table if not exists time_intervals
(
interval_start timestamp,
interval_end timestamp
);
-- *************************************************************************
-- Loop through the startdate adding each intval interval until enddate
-- *************************************************************************
repeat
select
case unitval
when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
when 'SECOND' then timestampadd(SECOND, intval, thisDate)
when 'MINUTE' then timestampadd(MINUTE, intval, thisDate)
when 'HOUR' then timestampadd(HOUR, intval, thisDate)
when 'DAY' then timestampadd(DAY, intval, thisDate)
when 'WEEK' then timestampadd(WEEK, intval, thisDate)
when 'MONTH' then timestampadd(MONTH, intval, thisDate)
when 'QUARTER' then timestampadd(QUARTER, intval, thisDate)
when 'YEAR' then timestampadd(YEAR, intval, thisDate)
end into nextDate;
insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
set thisDate = nextDate;
until thisDate >= enddate
end repeat;
END;
Similar example data scenario at the bottom of this post, where I built a similar function for SQL Server.
Answered by Ron Savage
Solution #2
You can use this with MSSQL. It is quite quick.
This can be wrapped up in a table-valued function or stored procedure, with the start and end dates parsed as variables.
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @startDate = '2011-01-01'
SET @endDate = '2011-01-31';
WITH dates(Date) AS
(
SELECT @startdate as Date
UNION ALL
SELECT DATEADD(d,1,[Date])
FROM dates
WHERE DATE < @enddate
)
SELECT Date
FROM dates
OPTION (MAXRECURSION 0)
GO
Dr. V’s edit, 2021/01: This method appealed to me, so I modified it to work with mySQL V8. Here’s the code, which I’ve wrapped into a procedure:
DELIMITER //
CREATE PROCEDURE dates_between (IN from_date DATETIME,
IN to_date DATETIME) BEGIN
WITH RECURSIVE dates(Date) AS
(
SELECT from_date as Date
UNION ALL
SELECT DATE_ADD(Date, INTERVAL 1 day) FROM dates WHERE Date < to_date
)
SELECT DATE(Date) FROM dates;
END//
DELIMITER ;
Answered by Richard
Solution #3
You can use MySQL’s user variables in the following way:
SET @num = -1;
SELECT DATE_ADD( '2009-01-01', interval @num := @num+1 day) AS date_sequence,
your_table.* FROM your_table
WHERE your_table.other_column IS NOT NULL
HAVING DATE_ADD('2009-01-01', interval @num day) <= '2009-01-13'
Because you add to @num the first time you use it, it is -1. Also, using “HAVING date sequence” will cause the user variable to increment twice for each row.
Answered by Andrew Vit
Solution #4
With BIRT reports, we faced a similar issue in that we wanted to report on days when there was no data. Because many days had no entries, the simplest approach for us was to create a simple table that had all dates and utilize that to retrieve ranges or connect to get zero values for those dates.
Every month, we run a process to guarantee that the table is populated for the next five years. This is how the table is made:
create table all_dates (
dt date primary key
);
There are certainly magical, complicated ways to do this with many DBMS’, but we always go with the easiest solution. The table’s storage requirements are small, and it makes queries considerably easier and portable. This type of approach is nearly always preferable in terms of performance because it doesn’t require per-row data processing.
The alternative way (which we’ve used before) is to make sure each date has its own item in the table. Periodically, we swept the table, adding zero entries for dates and/or times that didn’t exist. This may or may not be an option for you, depending on the data you have on hand.
If you truly don’t want to deal with keeping the all dates table populated, a stored procedure that returns a dataset with those dates is the way to go. Because you have to calculate the range every time it’s called rather than just pulling pre-calculated data from a table, this will nearly surely be slower.
But, to be honest, you could populate the table for 1000 years without running into serious data storage issues – 365,000 16-byte (for example) dates plus an index duplicating the date plus 20% overhead for safety, I’d estimate at around 14M [365,000 * 16 * 2 * 1.2 = 14,016,000 bytes]), a minuscule table in the grand scheme of things.
Answered by paxdiablo
Solution #5
Using a concept from this answer, you can create a table with the numbers 0 through 9 and use it to produce your date list.
CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
select adddate('2009-01-01', numlist.id) as `date` from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2009-01-01', numlist.id) <= '2009-01-13';
You can create a list of up to 1000 dates using this method. You can increase the size of the inner query by adding another cross join.
Answered by Logan5
Post is based on https://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates