开发者

generate days from date range

开发者 https://www.devze.com 2022-12-18 05:16 出处：网络
I would like开发者_JS百科 to run a query like select ... as days where `date` is between \'2010-01-20\' and \'2010-01-24\'
相关专题：betweendatetimesql

I would like开发者_JS百科 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

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

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;

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)

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.

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

The old school solution for doing this without a loop/cursor is to create a NUMBERS table, which has a single Integer column with values starting at 1. 

CREATE TABLE  `example`.`numbers` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

You need to populate the table with enough records to cover your needs:

INSERT INTO NUMBERS (id) VALUES (NULL);

Once you have the NUMBERS table, you can use:

SELECT x.start_date + INTERVAL n.id-1 DAY
  FROM NUMBERS n
  JOIN (SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d') AS start_date 
          FROM DUAL) x
 WHERE x.start_date + INTERVAL n.id-1 DAY <= '2010-01-24'

The absolute low-tech solution would be:

SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-21', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-22', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-23', '%Y-%m-%d')
 FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-24', '%Y-%m-%d')
 FROM DUAL

What would you use it for?

To generate lists of dates or numbers in order to LEFT JOIN on to. You would to this in order to see where there are gaps in the data, because you are LEFT JOINing onto a list of sequencial data - null values will make it obvious where gaps exist.

For Access 2010 - multiple steps required; I followed the same pattern as posted above, but thought I could help someone in Access. Worked great for me, I didn't have to keep a seeded table of dates.

Create a table called DUAL (similar to how the Oracle DUAL table works)

  • ID (AutoNumber)
  • DummyColumn (Text)
  • Add one row values (1,"DummyRow")

Create a query named "ZeroThru9Q"; manually enter the following syntax:

SELECT 0 AS a
FROM dual
UNION ALL
SELECT 1
FROM dual
UNION ALL
SELECT 2
FROM dual
UNION ALL
SELECT 3
FROM dual
UNION ALL
SELECT 4
FROM dual
UNION ALL
SELECT 5
FROM dual
UNION ALL
SELECT 6
FROM dual
UNION ALL
SELECT 7
FROM dual
UNION ALL
SELECT 8
FROM dual
UNION ALL
SELECT 9
FROM dual;

Create a query named "TodayMinus1KQ" (for dates before today); manually enter the following syntax:

SELECT date() - (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
  (SELECT *
   FROM ZeroThru9Q) AS a,

  (SELECT *
   FROM ZeroThru9Q) AS b,

  (SELECT *
   FROM ZeroThru9Q) AS c

Create a query named "TodayPlus1KQ" (for dates after today); manually enter the following syntax:

SELECT date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
  (SELECT *
   FROM ZeroThru9Q) AS a,

  (SELECT *
   FROM ZeroThru9Q) AS b,

  (SELECT *
   FROM ZeroThru9Q) AS c;

Create a union query named "TodayPlusMinus1KQ" (for dates +/- 1000 days):

SELECT MyDate
FROM TodayMinus1KQ
UNION
SELECT MyDate
FROM TodayPlus1KQ;

Now you can use the query:

SELECT MyDate
FROM TodayPlusMinus1KQ
WHERE MyDate BETWEEN #05/01/2014# and #05/30/2014#

Procedure + temporary table:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `days`(IN dateStart DATE, IN dateEnd DATE)
BEGIN

    CREATE TEMPORARY TABLE IF NOT EXISTS date_range (day DATE);

    WHILE dateStart <= dateEnd DO
      INSERT INTO date_range VALUES (dateStart);
      SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY);
    END WHILE;

    SELECT * FROM date_range;
    DROP TEMPORARY TABLE IF EXISTS date_range;

END

thx Pentium10 - you made me join stackoverflow :) - this is my porting to msaccess - think it'll work on any version:

SELECT date_value
FROM (SELECT a.espr1+(10*b.espr1)+(100*c.espr1) AS integer_value,
dateadd("d",integer_value,dateserial([start_year], [start_month], [start_day])) as date_value
FROM (select * from 
    (
    select top 1 "0" as espr1 from MSysObjects
    union all
    select top 1 "1" as espr2 from MSysObjects
    union all
    select top 1 "2" as espr3 from MSysObjects
    union all
    select top 1 "3" as espr4 from MSysObjects
    union all
    select top 1 "4" as espr5 from MSysObjects
    union all
    select top 1 "5" as espr6 from MSysObjects
    union all
    select top 1 "6" as espr7 from MSysObjects
    union all
    select top 1 "7" as espr8 from MSysObjects
    union all
    select top 1 "8" as espr9 from MSysObjects
    union all
    select top 1 "9" as espr9 from MSysObjects
    ) as a,
    (
    select top 1 "0" as espr1 from MSysObjects
    union all
    select top 1 "1" as espr2 from MSysObjects
    union all
    select top 1 "2" as espr3 from MSysObjects
    union all
    select top 1 "3" as espr4 from MSysObjects
    union all
    select top 1 "4" as espr5 from MSysObjects
    union all
    select top 1 "5" as espr6 from MSysObjects
    union all
    select top 1 "6" as espr7 from MSysObjects
    union all
    select top 1 "7" as espr8 from MSysObjects
    union all
    select top 1 "8" as espr9 from MSysObjects
    union all
    select top 1 "9" as espr9 from MSysObjects
    ) as b,
    (
    select top 1 "0" as espr1 from MSysObjects
    union all
    select top 1 "1" as espr2 from MSysObjects
    union all
    select top 1 "2" as espr3 from MSysObjects
    union all
    select top 1 "3" as espr4 from MSysObjects
    union all
    select top 1 "4" as espr5 from MSysObjects
    union all
    select top 1 "5" as espr6 from MSysObjects
    union all
    select top 1 "6" as espr7 from MSysObjects
    union all
    select top 1 "7" as espr8 from MSysObjects
    union all
    select top 1 "8" as espr9 from MSysObjects
    union all
    select top 1 "9" as espr9 from MSysObjects
    ) as c   
)  as d) 
WHERE date_value 
between dateserial([start_year], [start_month], [start_day]) 
and dateserial([end_year], [end_month], [end_day]);

referenced MSysObjects just 'cause access need a table countin' at least 1 record, in a from clause - any table with at least 1 record would do.

Elegant solution using new recursive (Common Table Expressions) functionality in MariaDB >= 10.3 and MySQL >= 8.0.

WITH RECURSIVE t as (
    select '2019-01-01' as dt
  UNION
    SELECT DATE_ADD(t.dt, INTERVAL 1 DAY) FROM t WHERE DATE_ADD(t.dt, INTERVAL 1 DAY) <= '2019-04-30'
)
select * FROM t;

The above returns a table of dates between '2019-01-01' and '2019-04-30'. It is also decently fast. Returning 1000 years worth of dates (~365,000 days) takes about 400ms on my machine.

As stated (or at least alluded to) in many of the wonderful answers already given, this problem is easily solved once you have a set of numbers to work with.

Note: The following is T-SQL but it's simply my particular implementation of general concepts already mentioned here and on the internet at large. It should be relatively simple to convert the code to your dialect of choice.

How? Consider this query:

SELECT DATEADD(d, N, '0001-01-22')
FROM Numbers -- A table containing the numbers 0 through N
WHERE N <= 5;

The above produces the date range 1/22/0001 - 1/27/0001 and is extremely trivial. There are 2 key pieces of information in the above query: the start date of 0001-01-22 and the offset of 5. If we combine these two pieces of information then we obviously have our end date. Thus, given two dates, generating a range can be broken down like so:

  • Find the difference between two given dates (the offset), easy:

    -- Returns 125 SELECT ABS(DATEDIFF(d, '2014-08-22', '2014-12-25'))

    Using ABS() here ensures that the date order is irrelevant.

  • Generate a limited set of numbers, also easy:

    -- Returns the numbers 0-2 SELECT N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM(SELECT 'A' AS S UNION ALL SELECT 'A' UNION ALL SELECT 'A')

    Notice we don't actually care what we're selecting FROM here. We just need a set to work with so that we count the number of rows in it. I personally use a TVF, some use a CTE, others use a numbers table instead, you get the idea. I advocate for using the most performant solution that you also understand.

Combining these two methods will solve our problem:

DECLARE @date1 DATE = '9001-11-21';
DECLARE @date2 DATE = '9001-11-23';

SELECT D = DATEADD(d, N, @date1)
FROM (
    SELECT N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
    FROM (SELECT 'A' AS S UNION ALL SELECT 'A' UNION ALL SELECT 'A') S
) Numbers
WHERE N <= ABS(DATEDIFF(d, @date1, @date2));

The above example is horrible code but demonstrates how everything comes together.

More Fun

I need to do this kind of thing a lot so I encapsulated the logic into two TVFs. The first generates a range of numbers and the second uses this functionality to generate a range of dates. The math is to ensure that input order doesn't matter and because I wanted to use the full range of numbers available in GenerateRangeSmallInt.

The following function takes ~16ms of CPU time to return the maximum range of 65536 dates.

CREATE FUNCTION dbo.GenerateRangeDate (   
    @date1 DATE,   
    @date2 DATE   
)   
RETURNS TABLE
WITH SCHEMABINDING   
AS   
RETURN (
    SELECT D = DATEADD(d, N + 32768, CASE WHEN @date1 <= @date2 THEN @date1 ELSE @date2 END)
    FROM dbo.GenerateRangeSmallInt(-32768, ABS(DATEDIFF(d, @date1, @date2)) - 32768)
);

GO

CREATE FUNCTION dbo.GenerateRangeSmallInt (
    @num1 SMALLINT = -32768
  , @num2 SMALLINT = 32767
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    WITH Numbers(N) AS (
        SELECT N FROM(VALUES
            (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256
        ) V (N)
    )
    SELECT TOP(ABS(CAST(@num1 AS INT) - CAST(@num2 AS INT)) + 1)
           N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + CASE WHEN @num1 <= @num2 THEN @num1 ELSE @num2 END - 1
    FROM Numbers A
       , Numbers B
);

try this.

SELECT TO_DATE('20160210','yyyymmdd') - 1 + LEVEL AS start_day 
from DUAL
connect by level <= (TO_DATE('20160228','yyyymmdd') + 1) - TO_DATE('20160210','yyyymmdd') ;

For anyone who wants this as a saved view (MySQL doesn't support nested select statements in views):

create view zero_to_nine as
    select 0 as n 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 date_range as
    select curdate() - INTERVAL (a.n + (10 * b.n) + (100 * c.n)) DAY as date
    from zero_to_nine as a
    cross join zero_to_nine as b
    cross join zero_to_nine as c;

You can then do

select * from date_range

to get

date
---
2017-06-06
2017-06-05
2017-06-04
2017-06-03
2017-06-02
...

You'd like to get the a date range.

In your example you'd like to get the dates between '2010-01-20' and '2010-01-24'

possible solution:

 select date_add('2010-01-20', interval row day) from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 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 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 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 6 union all select 7 union all select 8 union all select 9) t2, 
    (select 0 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 6 union all select 7 union all select 8 union all select 9) t3, 
    (select 0 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 6 union all select 7 union all select 8 union all select 9) t4, 
    (SELECT @row:=-1) r
 ) sequence
 where date_add('2010-01-20', interval row day) <= '2010-01-24'

Explanation

MySQL has a date_add function so

select date_add('2010-01-20', interval 1 day)

will give you

2010-01-21

The datediff function would let you know often you'd have to repeat this

select datediff('2010-01-24', '2010-01-20')

which returns

 4

Getting a list of dates in a date range boils down to creating a sequence of integer numbers see generate an integer sequence in MySQL

The most upvoted answer here has taken a similar approach as https://stackoverflow.com/a/2652051/1497139 as a basis:

SELECT @row := @row + 1 as row FROM 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
(SELECT @row:=0) r
limit 4

which will result in

row
1.0
2.0
3.0
4.0

The rows can now be used to create a list of dates from the given start date. To include the start date we start with row -1;

select date_add('2010-01-20', interval row day) from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
    (SELECT @row:=-1) r
 ) sequence
 where date_add('2010-01-20', interval row day) <= '2010-01-24'

A more generic answer that works in AWS MySQL.

select datetable.Date
from (
    select date_format(adddate(now(),-(a.a + (10 * b.a) + (100 * c.a))),'%Y-%m-%d') 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 now() - INTERVAL 14 Day and Now()
order by datetable.Date DESC

if you will ever need more then a couple days, you need a table.

Create a date range in mysql

then, 

select from days.day, count(mytable.field) as fields from days left join mytable on day=date where date between x and y;

Generate dates between two date fields

If you are aware with SQL CTE query, then this solution will helps you to solve your question

Here is example

We have dates in one table

Table Name: “testdate”

STARTDATE   ENDDATE
10/24/2012  10/24/2012
10/27/2012  10/29/2012
10/30/2012  10/30/2012

Require Result: 

STARTDATE
10/24/2012
10/27/2012
10/28/2012
10/29/2012
10/30/2012

Solution:

WITH CTE AS
  (SELECT DISTINCT convert(varchar(10),StartTime, 101) AS StartTime,
                   datediff(dd,StartTime, endTime) AS diff
   FROM dbo.testdate
   UNION ALL SELECT StartTime,
                    diff - 1 AS diff
   FROM CTE
   WHERE diff<> 0)
SELECT DISTINCT DateAdd(dd,diff, StartTime) AS StartTime
FROM CTE

Explanation: CTE Recursive query explanation

  • First part of query:

    SELECT DISTINCT convert(varchar(10), StartTime, 101) AS StartTime, datediff(dd, StartTime, endTime) AS diff FROM dbo.testdate

    Explanation: firstcolumn is “startdate”, second column is difference of start and end date in days and it will be consider as “diff” column

  • Second part of query:

    UNION ALL SELECT StartTime, diff-1 AS diff FROM CTE WHERE diff<>0

    Explanation: Union all will inherit result of above query until result goes null, So “StartTime” result is inherit from generated CTE query, and from diff, decrease - 1, so its looks like 3, 2, and 1 until 0

For example

STARTDATE   DIFF
10/24/2012  0
10/27/2012  0
10/27/2012  1
10/27/2012  2
10/30/2012  0

Result Specification

STARTDATE       Specification
10/24/2012  --> From Record 1
10/27/2012  --> From Record 2
10/27/2012  --> From Record 2
10/27/2012  --> From Record 2
10/30/2012  --> From Record 3

  • 3rd Part of Query

    SELECT DISTINCT DateAdd(dd,diff, StartTime) AS StartTime FROM CTE

    It will add day “diff” in “startdate” so result should be as below

Result 

STARTDATE
10/24/2012
10/27/2012
10/28/2012
10/29/2012
10/30/2012

Shorter than accepted answer, same idea:

(SELECT TRIM('2016-01-05' + INTERVAL a + b DAY) date
FROM
(SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9 ) d,
(SELECT 0 b UNION SELECT 10 UNION SELECT 20
UNION SELECT 30 UNION SELECT 40) m
WHERE '2016-01-05' + INTERVAL a + b DAY  <=  '2016-01-21')

One more solution for mysql 8.0.1 and mariadb 10.2.2 using recursive common table expressions:

with recursive dates as (
    select '2010-01-20' as date
    union all
    select date + interval 1 day from dates where date < '2010-01-24'
)
select * from dates;

It's a good idea with generating these dates on the fly. However, I do not feel myself comfortable to do this with quite large range so I've ended up with the following solution:

  1. Created a table "DatesNumbers" that will hold numbers used for dates calculation:
CREATE TABLE DatesNumbers (
    i MEDIUMINT NOT NULL,
    PRIMARY KEY (i)
)
COMMENT='Used by Dates view'
;
  1. Populated the table using above techniques with numbers from -59999 to 40000. This range will give me dates from 59999 days (~164 years) behind to 40000 days (109 years) ahead:
INSERT INTO DatesNumbers
SELECT 
    a.i + (10 * b.i) + (100 * c.i) + (1000 * d.i) + (10000 * e.i) - 59999 AS i
FROM 
  (SELECT 0 AS i 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,
  (SELECT 0 AS i 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,
  (SELECT 0 AS i 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,
  (SELECT 0 AS i 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,
  (SELECT 0 AS i 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 e
;
  1. Created a view "Dates":
SELECT
      i,
      CURRENT_DATE() + INTERVAL i DAY AS Date
FROM
    DatesNumbers

That's it.

  • (+) Easy to read queries
  • (+) No on the fly numbers generations
  • (+) Gives dates in the past and in the future and there is NO UNION in view for this as in this post.
  • (+) "In the past only" or "in the future only" dates could be filtered using WHERE i < 0 or WHERE i > 0 (PK)
  • (-) 'temporary' table & view is used

Alright.. Try this: http://www.devshed.com/c/a/MySQL/Delving-Deeper-into-MySQL-50/
http://dev.mysql.com/doc/refman/5.0/en/loop-statement.html
http://www.roseindia.net/sql/mysql-example/mysql-loop.shtml

Use that to, say, generate a temp table, and then do a select * on the temp table. Or output the results one at a time.
What you say you want to do can't be done with a SELECT statement, but it might be doable with things specific to MySQL.
Then again, maybe you need cursors: http://dev.mysql.com/doc/refman/5.0/en/cursors.html

For Oracle, my solution is:

select trunc(sysdate-dayincrement, 'DD') 
  from dual, (select level as dayincrement 
                from dual connect by level <= 30)

Sysdate can be changed to specific date and level number can be changed to give more dates.

if you want the list of dates between two dates:

create table #dates ([date] smalldatetime)
while @since < @to
begin
     insert into #dates(dateadd(day,1,@since))
     set @since = dateadd(day,1,@since)
end
select [date] from #dates

*fiddle here: http://sqlfiddle.com/#!6/9eecb/3469

set language  'SPANISH'
DECLARE @table table(fechaDesde datetime , fechaHasta datetime ) 
INSERT @table VALUES('20151231' , '20161231');
WITH x AS 
    (
        SELECT   DATEADD( m , 1 ,fechaDesde ) as fecha  FROM @table
        UNION ALL
        SELECT  DATEADD( m , 1 ,fecha )
        FROM @table t INNER JOIN x ON  DATEADD( m , 1 ,x.fecha ) <= t.fechaHasta
    )
SELECT LEFT( CONVERT( VARCHAR, fecha , 112 ) , 6 ) as Periodo_Id 
,DATEPART ( dd, DATEADD(dd,-(DAY(fecha)-1),fecha)) Num_Dia_Inicio
,DATEADD(dd,-(DAY(fecha)-1),fecha) Fecha_Inicio
,DATEPART ( mm , fecha ) Mes_Id
,DATEPART ( yy , fecha ) Anio
,DATEPART ( dd, DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha))) Num_Dia_Fin
,DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha)) ultimoDia
,datename(MONTH, fecha) mes
,'Q' + convert(varchar(10),  DATEPART(QUARTER, fecha)) Trimestre_Name
FROM x 
OPTION(MAXRECURSION 0)


DELIMITER $$
CREATE PROCEDURE GenerateRangeDates(IN dateStart DATE, IN dateEnd DATE)
BEGIN

    CREATE TEMPORARY TABLE IF NOT EXISTS dates (day DATE);

    loopDate: LOOP
        INSERT INTO dates(day) VALUES (dateStart); 
        SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY);

        IF dateStart <= dateEnd 
            THEN ITERATE loopDate;
            ELSE LEAVE loopDate;
        END IF;
    END LOOP loopDate;

    SELECT day FROM dates;
    DROP TEMPORARY TABLE IF EXISTS dates;

END 
$$

-- Call procedure
call GenerateRangeDates( 
        now() - INTERVAL 40 DAY,
        now()
    );

SQLite version of RedFilters top solution

select d.Date
from (
    select 
    date(julianday('2010-01-20') + (a.a + (10 * b.a) + (100 * c.a))) 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
) d
where 
d.Date between '2010-01-20' and '2010-01-24' 
order by d.Date

improved with weekday an joining a custom holiday table microsoft MSSQL 2012 for powerpivot date table https://gist.github.com/josy1024/cb1487d66d9e0ccbd420bc4a23b6e90e

with [dates] as (
    select convert(datetime, '2016-01-01') as [date] --start
    union all
    select dateadd(day, 1, [date])
    from [dates]
    where [date] < '2018-01-01' --end
)
select [date]
, DATEPART (dw,[date]) as Wochentag
, (select holidayname from holidaytable 
where holidaytable.hdate = [date]) 
as Feiertag
from [dates]
where [date] between '2016-01-01' and '2016-31-12'
option (maxrecursion 0)


WITH
  Digits AS (SELECT 0 D UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9),
  Dates AS (SELECT adddate('1970-01-01',t4.d*10000 + t3.d*1000 + t2.d*100 + t1.d*10 +t0.d) AS date FROM Digits AS t0, Digits AS t1, Digits AS t2, Digits AS t3, Digits AS t4)
SELECT * FROM Dates WHERE date BETWEEN '2017-01-01' AND '2017-12-31'

Can create a procedure also to create calendar table with timestmap different from day. If you want a table for each quarter

e.g.

2019-01-22 08:45:00
2019-01-22 09:00:00
2019-01-22 09:15:00
2019-01-22 09:30:00
2019-01-22 09:45:00
2019-01-22 10:00:00

you can use 

CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_calendar_table`()
BEGIN

select unix_timestamp('2014-01-01 00:00:00') into @startts;
select unix_timestamp('2025-01-01 00:00:00') into @endts;

if ( @startts < @endts ) then

    DROP TEMPORARY TABLE IF EXISTS calendar_table_tmp;

    CREATE TEMPORARY TABLE calendar_table_tmp (ts int, dt datetime); 

    WHILE ( @startts < @endts)
        DO 
        SET @startts = @startts + 900;
        INSERT calendar_table_tmp VALUES (@startts, from_unixtime(@startts));
    END WHILE;

END if;

END

and then manipulate through

select ts, dt from calendar_table_tmp;

that give you also ts

'1548143100', '2019-01-22 08:45:00'
'1548144000', '2019-01-22 09:00:00'
'1548144900', '2019-01-22 09:15:00'
'1548145800', '2019-01-22 09:30:00'
'1548146700', '2019-01-22 09:45:00'
'1548147600', '2019-01-22 10:00:00'

from here you can start to add other information such as 

select ts, dt, weekday(dt) as wd from calendar_table_tmp;

or create a real table with create table statement

Similar to D'Arcy Rittich's Answer, but for SQL SERVER

;WITH t AS
(
    SELECT n = a.n * 10 + b.n * 100 + c.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) b(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) c(n)
)
SELECT DATEADD(DAY, t.n, '2022-01-01')
FROM t
ORDER BY T.n;

or without CTE

SELECT DATEADD(DAY, nums.n, '2022-01-01') AS d FROM (
    SELECT n = a.n * 10 + b.n * 100 + c.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) b(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) c(n)
) nums
order by d
0

上一篇:

:下一篇

更多 问答 相关资讯：

精彩评论

暂无评论...
登录 注册
Qedev.com
验证码 换一张
取 消

关注公众号

热门标签

图文推荐

Delphi - Custom drawing a message list

Delphi - Custom drawing a message list
C++ header-only include pattern

C++ header-only include pattern
IE7 Margin Collapses Into Padding

IE7 Margin Collapses Into Padding
in CoffeeScript, how can I use a variable as a key in a hash?

in CoffeeScript, how can I use a variable as a key in a hash?
Interactive visualization of a graph in python [closed]

Interactive visualization of a graph in python [closed]
How to customise PHP MYSQL tables?

How to customise PHP MYSQL tables?
High quality, simple random password generator

High quality, simple random password generator
Image Recognition ApI in android

Image Recognition ApI in android

开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9