Entry

How can I get a sequence of days from MySQL?

May 18th, 2000 06:21
Nathan Wallace, Thimble Smith, Jan Dvorak

If you're really wanting to do it in MySQL, you could try something
like:

    mysql> select @now := to_days(now()),
        -> from_days(@now + 1) as first,
        -> from_days(@now + 2) as second,
        -> from_days(@now + 3) as third;
    +------------------------+------------+------------+------------+
    | @now := to_days(now()) | first      | second     | third      |
    +------------------------+------------+------------+------------+
    |                 730621 | 2000-05-17 | 2000-05-18 | 2000-05-19 |
    +------------------------+------------+------------+------------+
    1 row in set (0.01 sec)

Make sure you're using a recent version of 3.23, or it won't work.


You could also create a table with all possible dates.  Detailed
instructions follow.

1. Create the table:

create table Day (
   day date not null primary key
);


2. Stuff the table with dates.
The following script will do it for you:

#!/usr/bin/perl -w

use DBI;

# Edit the following command to match your set-up
$dbh = DBI->connect( "DBI:mysql:database=test:host=localhost",
                     "me", "mypasswd" ) or die;

my $sth = $dbh->prepare( <<xxx ) or die;
insert into Day( day )
    values ( from_days( to_days( '1970-01-01' ) + ? ) )
xxx

for ( my $i = 0; $i < 20000; ++$i ) {
    $sth->execute( $i ) or die;
}

$dbh->disconnect();

# This series ends some day in 2024
# This script takes around 15 seconds on my dual Celeron 450MHz, Linux
2.2.14
# End Perl


3. We're done.
This is going to be a read-only table from now on.
It's not too big:

[root@medved test]# ls -l Day.*
-rw-rw----   1 mysql    mysql       80000 May 16 17:54 Day.MYD
-rw-rw----   1 mysql    mysql      103424 May 16 17:54 Day.MYI
-rw-rw----   1 mysql    mysql        8552 May 16 17:54 Day.frm


4. You can issue a query such as the following one:

select day
  from Day
  where day between now() and date_add( now(), interval 2 day );
/* gives today, tomorrow and the day after tomorrow */