Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Unanswered: mysql: Loop for dates

    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

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    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

  3. #3
    Join Date
    Oct 2005
    Posts
    2
    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

  4. #4
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    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 )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •