Get a list of dates between two dates

Question

Using standard mysql functions is there a way to write a query that will return a list of days between two dates.

eg given 2009-01-01 and 2009-01-13 it would return a one column table with the 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 appears I have not been clear. I want to GENERATE this list. I have values stored in the database (by datetime) but want them to be aggregated on a left outer join to a list of dates as above (I am expecting null from the right side of some of this join for some days and will handle this).

Maleficence

I think best solution is described in the answer https://mcmap.net/q/49391/-generate-days-from-date-range – Arbitrament

Possible duplicate of generate days from date range – Phototype

Answer 1

I would use this stored procedure to generate the intervals you need into the temp table named time_intervals, then JOIN and aggregate your data table with the temp time_intervals table.

The procedure can generate intervals of all the different types you see specified in it:

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.

Oxyacetylene

I was personally hoping for something similar to generated sequences in PostgreSQL. – Eugenaeugene

If you are generating the data once, you may even use permanent tables and use this script to fill the missing dates. – Agronomics

I had to change the delimiter before the create procedure statement to make it works through phpMyAdmin (to avoid syntax error on declare statements) [code] DECLARE // [/code] – Vasculum

This solution is very powerful and flexible, unfotunately on mariaDB I am getting the error #1067 - Invalid default value for 'interval_end' when running call make_intervals('2009-01-01 00:00:00','2009-01-10 00:00:00',1,'DAY'); I am on version 5.7.28 – Lewendal

@Lewendal Set default values: interval_start timestamp NULL and interval_end timestamp NULL – Bahia

Answer 2

For MSSQL you can use this. It is VERY quick.

You can wrap this up in a table valued function or stored proc and parse in the start and end dates 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

Edit 2021/01 (Dr. V): I liked this solution and made it work for mySQL V8. Here is the code, wrapping it 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 ;

Panacea

What is the use of OPTION (MAXRECURSION 0) here? – Nape

Answer 3

You can use MySQL's user variables like this:

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'

@num is -1 because you add to it the first time you use it. Also, you can't use "HAVING date_sequence" because that makes the user variable increment twice for each row.

Owe

Answer 4

We had a similar problem with BIRT reports in that we wanted to report on those days that had no data. Since there were no entries for those dates, the easiest solution for us was to create a simple table that stored all dates and use that to get ranges or join to get zero values for that date.

We have a job that runs every month to ensure that the table is populated 5 years out into the future. The table is created thus:

create table all_dates (
    dt date primary key
);

No doubt there are magical tricky ways to do this with different DBMS' but we always opt for the simplest solution. The storage requirements for the table are minimal and it makes the queries so much simpler and portable. This sort of solution is almost always better from a performance point-of-view since it doesn't require per-row calculations on the data.

The other option (and we've used this before) is to ensure there's an entry in the table for every date. We swept the table periodically and added zero entries for dates and/or times that didn't exist. This may not be an option in your case, it depends on the data stored.

If you really think it's a hassle to keep the all_dates table populated, a stored procedure is the way to go which will return a dataset containing those dates. This will almost certainly be slower since you have to calculate the range every time it's called rather than just pulling pre-calculated data from a table.

But, to be honest, you could populate the table out for 1000 years without any serious data storage problems - 365,000 16-byte (for example) dates plus an index duplicating the date plus 20% overhead for safety, I'd roughly estimate at about 14M [365,000 * 16 * 2 * 1.2 = 14,016,000 bytes]), a minuscule table in the scheme of things.

Hege

Answer 5

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.

Kailey

"select '2019-01-01' as dt" what is this selector? Is it possible to just select a from and to field? – Galliot

A nice solution, but keep in mind it might not work for larger date ranges, due to something similar to what we used to call a StackOverflow error :) Each rows (i.e. date) adds a new function call to a call stack. The default limit is 1000. You can increase it by modifying cte_max_recursion_depth if you think that would be worth the risk that might come with such approach. – Fallible

Answer 6

Borrowing an idea from this answer, you can set up a table with 0 through 9 and use that to generate your list of dates.

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';

This will allow you to generate a list of up to 1000 dates. If you need to go larger, you can add another cross join to the inner query.

Primrose

This solution works much better. But it has an issue with UNIX_TIMESTAMP() - it gives the results like 1231185600.000000; the miliseconds part after the decimal point; while - SELECT UNIX_TIMESTAMP(ADDDATE('2009-01-01', 0)) AS date; does NOT result that part. – Agronomics

Answer 7

For Access (or any SQL language)

1. Create one table that has 2 fields, we'll call this table tempRunDates:
   --Fields fromDate and toDate
   --Then insert only 1 record, that has the start date and the end date.

2. Create another table: Time_Day_Ref
   --Import a list of dates (make list in excel is easy) into this table.
   --The field name in my case is Greg_Dt, for Gregorian Date
   --I made my list from jan 1 2009 through jan 1 2020.

3. Run the query:

   SELECT Time_Day_Ref.GREG_DT
   FROM tempRunDates, Time_Day_Ref
   WHERE Time_Day_Ref.greg_dt>=tempRunDates.fromDate And greg_dt<=tempRunDates.toDate;
   

Easy!

Infernal

Answer 8

CREATE FUNCTION [dbo].[_DATES]
(
    @startDate DATETIME,
    @endDate DATETIME
)
RETURNS 
@DATES TABLE(
    DATE1 DATETIME
)
AS
BEGIN
    WHILE @startDate <= @endDate
    BEGIN 
        INSERT INTO @DATES (DATE1)
            SELECT @startDate   
    SELECT @startDate = DATEADD(d,1,@startDate) 
    END
RETURN
END

Thundersquall

Answer 9

You can use this

SELECT CAST(cal.date_list AS DATE) day_year
FROM (
  SELECT SUBDATE('2019-01-01', INTERVAL 1 YEAR) + INTERVAL xc DAY AS date_list
  FROM (
        SELECT @xi:=@xi+1 as xc from
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc3,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc4,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc5,
        (SELECT @xi:=-1) xc0
    ) xxc1
) cal
WHERE cal.date_list BETWEEN '2019-01-01' AND '2019-12-31'
ORDER BY cal.date_list DESC;

Callis

Source:- shayanderson.com/mysql/… – Callis

Hi @Dixon, thanks for this one! This one worked very well for my purpose; The same results but without using a function, stored procedure or CTE. – Asia

Why we are doing (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xcn can anyone explain? – Dive

Answer 10

Typically one would use an auxiliary numbers table you usually keep around for just this purpose with some variation on this:

SELECT *
FROM (
    SELECT DATEADD(d, number - 1, '2009-01-01') AS dt
    FROM Numbers
    WHERE number BETWEEN 1 AND DATEDIFF(d, '2009-01-01', '2009-01-13') + 1
) AS DateRange
LEFT JOIN YourStuff
    ON DateRange.dt = YourStuff.DateColumn

I've seen variations with table-valued functions, etc.

You can also keep a permanent list of dates. We have that in our data warehouse as well as a list of times of day.

Ubangi

Answer 11

We used this in our HRMS System you will find it useful

SELECT CAST(DAYNAME(daydate) as CHAR) as dayname,daydate
    FROM
    (select CAST((date_add('20110101', interval H.i*100 + T.i*10 + U.i day) )as DATE) as daydate
      from erp_integers as H
    cross
      join erp_integers as T
    cross
      join erp_integers as U
     where date_add('20110101', interval H.i*100 + T.i*10 + U.i day ) <= '20110228'
    order
        by daydate ASC
        )Days

Aldric

Answer 12

This solution is working with MySQL 5.0
Create a table - mytable.
The schema does not material. What matters is the number of rows in it.
So, you can keep just one column of type INT with 10 rows, values - 1 to 10.

SQL:

set @tempDate=date('2011-07-01') - interval 1 day;
select
date(@tempDate := (date(@tempDate) + interval 1 day)) as theDate
from mytable x,mytable y
group by theDate
having theDate <= '2011-07-31';

Limitation: The maximum number of dates returned by above query will be
(rows in mytable)*(rows in mytable) = 10*10 = 100.

You can increase this range by changing form part in sql:
from mytable x,mytable y, mytable z
So, the range be 10*10*10 =1000 and so on.

Allayne

Answer 13

No temp table | No procedure | No functions

The example below present one month date range - from day 1 to the last day of the same month. You can change the date range by playing with @e `INTERVAL` values.

SET @s := CAST('2021-12-01' AS DATE); -- start at 2021-12-01
SET @e := @s + INTERVAL 1 MONTH - INTERVAL 1 DAY; -- ends at 2021-12-31

WITH RECURSIVE d_range AS
(
       SELECT @s AS gdate -- generated dates
       UNION ALL
       SELECT gdate + INTERVAL 1 day
       FROM   d_range
       WHERE  gdate < @e
)
SELECT *
FROM   d_range;

Result:

| gdate       |
+------------+
| 2021-12-01 |
| 2021-12-02 |
| 2021-12-03 |
| 2021-12-04 |
| 2021-12-05 |
| 2021-12-06 |
| 2021-12-07 |
| 2021-12-08 |
| 2021-12-09 |
| 2021-12-10 |
| 2021-12-11 |
| 2021-12-12 |
| 2021-12-13 |
| 2021-12-14 |
| 2021-12-15 |
| 2021-12-16 |
| 2021-12-17 |
| 2021-12-18 |
| 2021-12-19 |
| 2021-12-20 |
| 2021-12-21 |
| 2021-12-22 |
| 2021-12-23 |
| 2021-12-24 |
| 2021-12-25 |
| 2021-12-26 |
| 2021-12-27 |
| 2021-12-28 |
| 2021-12-29 |
| 2021-12-30 |
| 2021-12-31 |
+------------+
-> 31 rows in set (0.037 sec)

Note. This query uses RECURSION there for it is quite slow 31 rows in set (0.037 sec), when selecting longer date ranges it will get even slower.

Diner

Answer 14

I would use something similar to this:

DECLARE @DATEFROM AS DATETIME
DECLARE @DATETO AS DATETIME
DECLARE @HOLDER TABLE(DATE DATETIME)

SET @DATEFROM = '2010-08-10'
SET @DATETO = '2010-09-11'

INSERT INTO
    @HOLDER
        (DATE)
VALUES
    (@DATEFROM)

WHILE @DATEFROM < @DATETO
BEGIN

    SELECT @DATEFROM = DATEADD(D, 1, @DATEFROM)
    INSERT 
    INTO
        @HOLDER
            (DATE)
    VALUES
        (@DATEFROM)
END

SELECT 
    DATE
FROM
    @HOLDER

Then the @HOLDER Variable table holds all the dates incremented by day between those two dates, ready to join at your hearts content.

Lepidolite

Hello, I am trying to use the code and insert the output into a table I defined DateTest with column datefill of type DATETIME...but failing...could u provide the code that would work with that please? Note: Using SQL Server here Thanks :) – Dump

Answer 15

I've been fighting with this for quite a while. Since this is the first hit on Google when I searched for the solution, let me post where I've gotten so far.

SET @d := '2011-09-01';
SELECT @d AS d, cast( @d := DATE_ADD( @d , INTERVAL 1 DAY ) AS DATE ) AS new_d
  FROM [yourTable]
  WHERE @d <= '2012-05-01';

Replace [yourTable] with a table from your database. The trick is that the number of rows in the table you select must be >= the number of dates you want to be returned. I tried using the table placeholder DUAL, but it would only return one single row.

Wellestablished

Interesting approach. But .. isn't this essentially what Vihang suggested above ;) ? – Field

Answer 16

select * from table_name where col_Date between '2011/02/25' AND DATEADD(s,-1,DATEADD(d,1,'2011/02/27'))

Here, first add a day to the current endDate, it will be 2011-02-28 00:00:00, then you subtract one second to make the end date 2011-02-27 23:59:59. By doing this, you can get all the dates between the given intervals.

output:
2011/02/25
2011/02/26
2011/02/27

Hipster

Answer 17

DELIMITER $$  
CREATE PROCEDURE popula_calendario_controle()
   BEGIN
      DECLARE a INT Default 0;
      DECLARE first_day_of_year DATE;
      set first_day_of_year = CONCAT(DATE_FORMAT(curdate(),'%Y'),'-01-01');
      one_by_one: LOOP
         IF dayofweek(adddate(first_day_of_year,a)) <> 1 THEN
            INSERT INTO calendario.controle VALUES(null,150,adddate(first_day_of_year,a),adddate(first_day_of_year,a),1);
         END IF;
         SET a=a+1;
         IF a=365 THEN
            LEAVE one_by_one;
         END IF;
      END LOOP one_by_one;
END $$

this procedure will insert all dates from the beginning of the year till now, just substitue the days of the "start" and "end", and you are ready to go!

Cyanate

How is the line IF a=365 THEN affected by leap years? – Majormajordomo

Also, what is the significance of the number 150 on line 9 ? This code is a "pat answer" without much actual explanation. – Majormajordomo

Answer 18

I needed a list with all months between 2 dates for statistics. The 2 dates are the start and enddate from a subscription. So the list shows all months and the amount of subscriptions per month.

MYSQL

CREATE PROCEDURE `get_amount_subscription_per_month`()
BEGIN
   -- Select the ultimate start and enddate from subscribers
   select @startdate := min(DATE_FORMAT(a.startdate, "%Y-%m-01")), 
          @enddate := max(DATE_FORMAT(a.enddate, "%Y-%m-01")) + interval 1 MONTH
   from subscription a;

   -- Tmp table with all months (dates), you can always format them with DATE_FORMAT) 
   DROP TABLE IF EXISTS tmp_months;
   create temporary table tmp_months (
      year_month date,
      PRIMARY KEY (year_month)
   );


   set @tempDate=@startdate;  #- interval 1 MONTH;

   -- Insert every month in tmp table
   WHILE @tempDate <= @enddate DO
     insert into tmp_months (year_month) values (@tempDate);
     set @tempDate = (date(@tempDate) + interval 1 MONTH);
   END WHILE;

   -- All months
   select year_month from tmp_months;

   -- If you want the amount of subscription per month else leave it out
   select mnd.year_month, sum(subscription.amount) as subscription_amount
   from tmp_months mnd
   LEFT JOIN subscription ON mnd.year_month >= DATE_FORMAT(subscription.startdate, "%Y-%m-01") and mnd.year_month <= DATE_FORMAT(subscription.enddate, "%Y-%m-01")
   GROUP BY mnd.year_month;

 END

Whitehall

Answer 19

No Function, No Procedures

SET @s := '2020-01-01';
SET @e := @s + INTERVAL 1 YEAR - INTERVAL 1 DAY; -- set end date to select

SELECT CAST((DATE(@s)+INTERVAL (H+T+U) DAY) AS DATE) d -- generate a list of 400 days starting from @s date so @e can not be more then @s + 
FROM ( SELECT 0 H
    UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
  ) H CROSS JOIN ( SELECT 0 T
    UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
    UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
    UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
  ) T CROSS JOIN ( SELECT 0 U
    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
  ) U
 -- generated lenght of date list can be calculated as fallow 1 * H(3 + 1) * T(9 + 1) * U(9 + 1) = 400
 -- it is crucial to preserve the numbering convention as 1, 2 ... 20, 30..., 100, 200, ... to retrieve chronological date selection
 -- add more UNION 400, 500, 600, ... H(6 + 1) if you want to select from more than 700 days!
WHERE
  (DATE(@s+INTERVAL (H+T+U) DAY)) <= DATE((@e))
ORDER BY d;

UNIONS define the length and range of available dates to elect from. So always make sure to make it log enough.

SELECT 1 * (3 + 1) * (9 + 1) * (9 + 1); -- 400 days starting from from @s

Based on this article
Tested and working as expected

Diner

Answer 20

Improving on @brad answer, I used a CTE with a cursor:

DECLARE cursorPeriod CURSOR FOR SELECT * FROM (
   WITH RECURSIVE period as
        (
             SELECT '2019-01-01' as dt
             UNION
             SELECT DATE_ADD(period.dt, INTERVAL 1 DAY) FROM period WHERE DATE_ADD(period.dt, INTERVAL 1 DAY) <= '2019-04-30'
        )
   SELECT DATE(dt) FROM period ORDER BY dt ) AS derived_table;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

Unique

Answer 21

Portable solution

-- Generate 
WITH 
tdates AS (
  WITH   t19 AS (
          SELECT  0 i 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 )    
   SELECT  adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) datei 
   FROM  t19 AS t0, t19 AS t1, t19 AS t2, t19 AS t3,  t19 AS t4 
)

select * from tdates
where datei between '2012-02-10' and '2012-02-15'

Caitlyncaitrin

Answer 22

Create a stored procedure which takes two parameters a_begin and a_end. Create a temporary table within it called t, declare a variable d, assign a_begin to d, and run a WHILE loop INSERTing d into t and calling ADDDATE function to increment the value d. Finally SELECT * FROM t.

Awaken

Actually I believe a permanent table would be better for speed of execution (has minimal storage requirements). It's faster to pre-calculate the dates into a table and extract when needed, than to create ranges every time you need them. – Hege

@Pax, it depends if the gained speed improvement is worth the table maintenance. For example, if the report generation takes 3 seconds and running WHILE loop to generate dates takes 0.001 seconds, I'd argue that the performance gain is ignorable. Knuth: Premature optimization is root of all evil. – Awaken

@eed3si9n, premature optimization, yes, not all optimization :-) If your example were correct then yes, I'd agree with you, but it should be measured for certainty. Table maint is gone if you generate it 1000 years out (a one time cost) as I suggested but those seconds will add up. – Hege

Answer 23

I am using Server version: 5.7.11-log MySQL Community Server (GPL)

Now we will solve this in a simple way.

I have created a table named "datetable"

mysql> describe datetable;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| colid   | int(11) | NO   | PRI | NULL    |       |
| coldate | date    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

now, wee will see the inserted records within.

mysql> select * from datetable;
+-------+------------+
| colid | coldate    |
+-------+------------+
|   101 | 2015-01-01 |
|   102 | 2015-05-01 |
|   103 | 2016-01-01 |
+-------+------------+
3 rows in set (0.00 sec)

and here our query to fetch records within two dates rather than those dates.

mysql> select * from datetable where coldate > '2015-01-01' and coldate < '2016-01-01';
+-------+------------+
| colid | coldate    |
+-------+------------+
|   102 | 2015-05-01 |
+-------+------------+
1 row in set (0.00 sec)

hope this would help many ones.

Determinism

Downvoted, without any reason, this SQL is fine and in working scenario. – Determinism

It doesn't answer the question – Kailey