• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

MySQL auto fill table with dates

I have a table named "dbDiary" with fields "fDate" and "fDay".
I want to fill "fDate" field with dates starting July 1st, 2009, in format YYYY-MM-DD and for each value to have the "fDay" field populated with the day number (Monday=1, ..., Sunday=7) - up to 2012, December 31st.

Example of dbDiary table:      

fDate           fTable
--------------------------------
20090713      1
20090714      2
20090715      3
20090716      4
20090717      5
20090718      6
20090719      7
20090720      1
20090721      2
20090722      3
20090723      4
20090724      5
20090725      6
20090726      7
20090727      1
20090728      2
etc..

How can I do this?
0
mechanism
Asked:
mechanism
  • 3
  • 2
1 Solution
 
Steve BinkCommented:
You could build a simple stored procedure to make this happen, but the better question is why you feel you need to.  What is the purpose behind populating the table in this manner?
0
 
snoyes_jwCommented:
Insert one record, then insert...select a few times to get the rest of the records:
INSERT INTO dbDiary (fDate) VALUES ('2009-07-01');
 
INSERT INTO dbDiary (fDate) SELECT fDate + INTERVAL (SELECT COUNT(*) FROM dbDiary) DAY FROM dbDiary;
-- Repeat that last insert 10 times, which will populate the table up through 2015
 
DELETE FROM dbDiary WHERE fDate > '2012-12-31';
UPDATE dbDiary SET fDay = DAYOFWEEK(fDate);

Open in new window

0
 
mechanismAuthor Commented:
Answering to routinet.

Your comment is valid.
I have a number of tables that need to be loaded each Monday, Tuesday, Wednesday etc. On each day (first column) I want to load files in tables ending in '1' (for example, file blabla.txt into table BlaBla_1, on next day, suppose a Tuesday, file blabla.txt into table BlaBla_2, next day on BlaBla_3 etc).
I need to have a file from each day of the week from another system into MySql but on a weekly basis. Again, using the same example, after a week, I need to overwrite table BlaBla_1 with the new blabla.txt file etc.
 
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mechanismAuthor Commented:
Answering to snoyes_jw

Please allow me to get back to you, I am now checking this.. Thank you for comment..
0
 
snoyes_jwCommented:
Instead of all those separate tables, why not one table, with a field that indicates the day of the week? Then you can delete the old records and insert the new ones, without all the dynamic queries and such that multiple tables imply.

Using 5.1's partitioning would make it even easier.
0
 
mechanismAuthor Commented:
Thank you, it worked, and I also made it in a stored procedure.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now