mysql: Loop for dates

vvekaria · #1 (**permalink**) 10-31-05, 12:55

Hi,

I need to insert dates into a mysql temporary table. I need to explicitly state the date in the script so next time I just come in and change that date and the columns get filled using that date as it's source.

I need (from 15th Oct 2005 for example):

Column called FN will hold daily dates for the last 2 weeks (fortnight);
Column called YTD will hold daily dates from year to date;
Column called PrevYear will hold daily dates for last 12 months;

Any ideas? Many Thanks in advance!

Regards,
Vijay

jfulton · #2 (**permalink**) 10-31-05, 16:36

What is your scripting language? Or are you writing a stored procedure?

Just write something like this for each table:

Code:

populateFortnights(end) {
   start = end - 2 weeks;
   currentDate = start;
   while (currentDate <= end) {
        insert(currentDate);
        currentDate += 1 day;  //use DATE_ADD() if mysql proc
   }
}

(you are probably going to want to use separate tables rather than 3 columns in 1 table because FN=14 rows while PrevYear=365...hmmm...why do you need this data in a table??? And if you really really do, why not just populate one table once with every date?)

anyways, here's some links you may want to check out.
php: http://us2.php.net/manual/en/function.time.php#32487
mysql 5.0: http://dev.mysql.com/doc/refman/5.0/...procedure.html

vvekaria · #3 (**permalink**) 11-01-05, 11:32

Apologies - I have no idea of the answers to the questions you are asking!

I am using an application called WinSQL Lite. I simply connect to a database we have and normally only execute simple Select statements.

For this problem so far I have the following:

Create Table #AnalysisDates (FNDate date)
go
Insert #AnalysisDates(FNDate) values('Oct-16-2005')

These commands are executed one line at a time.

I guess ideally the Insert statement needs to be in a loop of some sort such that column FNDate is filled with dates from 16th October 2005 to 31st October 2005. Is this possible?

Any help is gratefully received!

Many Thanks,
Vijay

jfulton · #4 (**permalink**) 11-03-05, 10:39

If you are using MySQL 5.0+ you should be able to write a stored procedure. I've never used WinSQL Lite, but assume that it has the capability to do this. If you're on an older version of MySQL, maybe WinSQL Lite can store scripts???

Also, just as an aside, what are you using the temporary date table for? If you're just using it to create a date range based on one date, you could do something like:

Code:

SELECT *
FROM mytable
WHERE thedate
BETWEEN DATE( '2005-11-1' )
AND DATE_ADD( '2005-11-1', INTERVAL 31 DAY )

Internet Services

Web Development

Digital Marketing

Consumer Tech