generate days from date range

Question

I would like to run a query like

select ... as days where `date` is between '2010-01-20' and '2010-01-24'

And return data like:

days
----------
2010-01-20
2010-01-21
2010-01-22
2010-01-23
2010-01-24

Solution 1

This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last 10,000 days, and could be extended to go as far back or forward as you wish.

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Date between '2010-01-20' and '2010-01-24' 

Output:

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

Notes on Performance

Testing it out here, the performance is surprisingly good: the above query takes 0.0009 sec.

If we extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it runs in 0.0458 sec.

Incidentally, this is a very portable technique that works with most databases with minor adjustments.

SQL Fiddle example returning 1,000 days

Solution 2

Here is another variation using views:

CREATE VIEW digits AS
  SELECT 0 AS digit UNION ALL
  SELECT 1 UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4 UNION ALL
  SELECT 5 UNION ALL
  SELECT 6 UNION ALL
  SELECT 7 UNION ALL
  SELECT 8 UNION ALL
  SELECT 9;

CREATE VIEW numbers AS
  SELECT
    ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
  FROM
    digits as ones,
    digits as tens,
    digits as hundreds,
    digits as thousands;

CREATE VIEW dates AS
  SELECT
    SUBDATE(CURRENT_DATE(), number) AS date
  FROM
    numbers;

And then you can simply do (see how elegant it is?):

SELECT
  date
FROM
  dates
WHERE
  date BETWEEN '2010-01-20' AND '2010-01-24'
ORDER BY
  date

Update

It is worth noting that you will only be able to generate past dates starting from the current date. If you want to generate any kind of dates range (past, future, and in between), you will have to use this view instead:

CREATE VIEW dates AS
  SELECT
    SUBDATE(CURRENT_DATE(), number) AS date
  FROM
    numbers
  UNION ALL
  SELECT
    ADDDATE(CURRENT_DATE(), number + 1) AS date
  FROM
    numbers;

Solution 3

Accepted answer didn't work for PostgreSQL (syntax error at or near "a").

The way you do this in PostgreSQL is by using generate_series function, i.e.:

SELECT day::date
FROM generate_series('2010-01-20', '2010-01-24', INTERVAL '1 day') day;

    day
------------
 2010-01-20
 2010-01-21
 2010-01-22
 2010-01-23
 2010-01-24
(5 rows)

Solution 4

Using a recursive Common Table Expression (CTE), you can generate a list of dates, then select from it. Obviously you normally wouldn't want to create three million dates, so this just illustrates the possibilities. You could simply limit the date range inside the CTE and omit the where clause from the select statement using the CTE.

with [dates] as (
    select convert(datetime, '1753-01-01') as [date] --start
    union all
    select dateadd(day, 1, [date])
    from [dates]
    where [date] < '9999-12-31' --end
)
select [date]
from [dates]
where [date] between '2013-01-01' and '2013-12-31'
option (maxrecursion 0)

On Microsoft SQL Server 2005, generating the CTE list of all possible dates took 1:08. Generating one hundred years took less than a second.

Solution 5

MSSQL Query

select datetable.Date 
from (
    select DATEADD(day,-(a.a + (10 * b.a) + (100 * c.a)),getdate()) AS Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
     union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a

    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
     union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b

    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
     union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) datetable
where datetable.Date between '2014-01-20' and '2014-01-24' 
order by datetable.Date DESC

Output

Date
-----
2014-01-23 12:35:25.250
2014-01-22 12:35:25.250
2014-01-21 12:35:25.250
2014-01-20 12:35:25.250

View more solutions

Comments

[Pentium10]

There is no other problem attached to this question. The above question is the problem, mastering SQL courses.

[Derek Adair]

Do you just need an array of dates based on a selected date-range?

[Pentium10]

I am thinking of a usage, to find you a problem... If you get a task to fill in some missing records in your table. And you have to run a query for each day I am thinking something like insert into table select ... as days date between '' and ''

[Nanne]

An example for its use would be to generate statistics, and include a row for dates you have no data on. If you are doing some sort of group-by it can be much quicker to actually generate all the information in SQL and add it into whatever format you need, instead of dumping your data as-is to your language, and start looping and adding your empties.

[Josh Diehl]

@Nanne that is precisely why I saved this question. I need the above to LEFT JOIN into data which may not exist for certain dates.

[outis]

Covered by "Get a list of dates between two dates" (though this question is narrower in focus; specifically, the other is RDBMS-agnostic).

[OMG Ponies]

You'll see better performance if you change UNION to UNION ALL - it's wasting time checking for duplicates to remove that don't exist. It's overcomplicated IMO though - if you're going to construct a resultset using UNIONs, why not just specify the date and be done with it?

[D'Arcy Rittich]

why not just specify the date and be done with it - because the above method allows you to create arbitrarily large sets of numbers (and dates) requiring no table creation, that would be painful to hard-code in the manner you are suggesting. Obviously for 5 dates it is overkill; but even then, if you are joining against a table where you do not know the dates in advance, but just the potential min and max values, it makes sense.

[OMG Ponies]

It's "painful" to just use the DATETIME function in place of the UNION statement you've already created? It alleviates any need for the logic you had to add. Hence - you've overcomplicated the query. The UNION statement, either way, is not scalable - specifying a date or number, who wants to update it to accommodate say 20 or 30 dates?

[OMG Ponies]

The DUAL table is supported by Oracle and MySQL to use as a stand-in table in the FROM clause. It doesn't exist, selecting values from it will return whatever the value is. The idea was to have the stand-in because a SELECT query requires a FROM clause specifying at least one table.

[Pentium10]

Thanks, can you please edit and update to use UNION ALL, then I will flag as answer.

[Pentium10]

why have you posted this, since the above reply does not need a table and provides the solution?

[joe]

It's really nice to see an answer to the question, not endless comments how it cannot, or should not, be done. Most things can be done, and "should" is only meaningful in context, which differs for everyone. This answer helped me, even though I am well aware there are better ways in most situations.

[spencer7593]

+1 This is the approach I use to generate a list of dates, or more often, a list of datetime values on a specified interval (e.g. 15 minutes). This same approach works in SQL Server as well. In Oracle, we can use (the more terse) SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 1000 to get a list of integer values.

[xjshiya]

@RedFilter, this is awesome. However, when I changed the dates to 2013-3-1 and 2013-3-13, The output ranges only from 2013-3-1 up to 2013-3-5. I'm experiencing the same problem right now so I need help. stackoverflow.com/questions/15223032/… thanks.

[D'Arcy Rittich]

@xjshiya The query as written goes back from the current date (curdate()). Change curdate() to '2014-01-01' and it will go back from that date instead.

[Artur]

This is why I hate SQL - it is so ugly

[corg]

Needed the following tweak for PostgreSQL: the "INTERVAL (a.a+...*c.a) DAY" part should be "(a.a+...*c.a) * INTERVAL '1' DAY" - Anyways, very helpful answer, works with heroku dataclips, thx!

[vasanth]

This doesn't work in all the cases. SELECT date FROM dates WHERE date BETWEEN '2014-12-01' AND '2014-12-28' ORDER BY date

[Stéphane]

Good call @user927258. This is because the first view dates mentioned above computes the dates starting from the current date, which is why you won't be able to retrieve dates set in the future. Answer from @RedFilter suffers from the same design flaw. I have added a workaround in my answer though.

[Erran Morad]

@RedFilter - I get 0 rows when I execute your query.

[D'Arcy Rittich]

@BoratSagdiyev You are saying the SQL Fiddle example returns zero rows for you? Works fine for me.

[Erran Morad]

@RedFilter - I try to run it on my machine in MySQL, it fail.

[D'Arcy Rittich]

@BoratSagdiyev Since my MySQL demo works, sounds like you need to ask a new question on SO.

[gadelat]

How do I use this in trigger?

[D'Arcy Rittich]

@gadelat The same way you would use any other SQL in a trigger :)

[swapab]

How would you join this query across a table? Basically am trying to count the number of entries grouped by date.

[D'Arcy Rittich]

@swap.nil Use the date query as a sub-query (derived table) and join it against your table and then apply the group by.

[swapab]

@RedFilter Thank you for suggestion. Would be amazing if you have some eyes on trials I did here

[Brian]

This generates historical dates leading up to CURDATE(). To generate dates into the future starting from CURDATE(), change to SELECT CURDATE() + INTERVAL().

[Noel Baron]

Those of you who cannot get this query to work: Please slap yourself in the face and then re-read the OP's comment about this query generating 1000 dates. Since 2010 was more than 1000 days ago, you'll need to adjust the query accordingly.

[Vignesh]

One of the best queries I have ever seen, I need to become a mysql master like you.

[Bacon Bits]

+1 for actually creating a permanent numbers table instead of making the RDBMS build it with every time you need the query. Auxiliary tables aren't evil, people!

[AdamMc331]

This is incredible, and it runs well? I regret that I can only give one upvote.

[madz]

In the case of range, is not that better to put bigger date instead of current date? In the given example I mean '2010-01-24' instead of curdate() then just check > '2010-01-20' instead of between?

[Ricardo C]

If I had only scrolled down a bit more... sigh. Anyways, thank you. I added a CAST( <expression> AS DATE) to remove the time on my version. Also used where a.Date between GETDATE() - 365 AND GETDATE() ...if you run your query today it would give no rows if you dont notice the dates in the WHERE =P

[Stewart]

Using some views definitely simplifies the queries, and makes it reusable. Although they are essentially doing the same thing, all those UNION clauses look weird in a single SQL statement.

[Ferrybig]

This answer depends on the current year, from 2016, you cannot generate dates in 2010

[zed]

If you want to generate dates in the future, use select curdate() + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date. My eyes!

[Wolfgang Fahl]

I modified the answer to go back as much as 10.000 days so it will work in 2018 for the given example. It's a bit slower this way.

[mpapec]

Curious whether union elimination in dates view would result in some performance gains.