How to store and query data into Database depending upon its arrival time ?

tags:

mysql

views:

answers:

How to store and query data into Database depending upon its arrival time ?

I have unpredictable frequency of Incoming csv data file which I need to store into mysql database. I can get multiple csv file in a day or I can get one csv file in one week.

CSV files contains data that is valid for the day when it was received meaning it contains offers details which are valid for only the day file was send and so my question is that is it possible to store this data into mysql database according to dates of its arrival.

Meaning If I received csv file on 19th December, 2009 and than on 20th December, 2009 than in mysql database can I have some sort of way to partition such that I will have heading 19th December, 2009 and than under it would be all offer data relevant for that day and than will have 20th December, 2009 which will then have all offer details relevant to that day instead of having generic tables and entries as this is the explicitly mentioned in client's requirement that they do not want generic tables and entries.

In your table, add a column of a DATE type, which you set to the current date when you process the CSV file. (Assuming you process it when it arrives).

nos 2009-12-20 20:02:57

In addition to my comment above, I guess in that case I should also have another column of VARCHAR type which would contain name of the Campaign which generated the CSV data file.

Rachel 2009-12-20 20:07:09

If each entry has a timestamp, then why not just include that field as a datetime field in the MySQL database?

wallyk 2009-12-20 20:04:07

Thing to notice here is that there can be many different sources of input as this csv file are generated on the basis of campaign run and there can be multiple campaign run at the same time.

Rachel 2009-12-20 20:06:01

you could set a column to equal the creation time of the file. (The closest thing on most unix fs would be the ctime. In php filectime()).

If you actually need to create new tables or columns named after the arrival times, your script would need to call create table, or alter table add column, etc with the formatted date string.

jspcal 2009-12-20 20:05:34

The file I will be getting will be in batch meaning there would be totally new file if there are addition, deletion or updates. This operations would not be merged onto previous version of CSV file and so I am not very sure of how setting column equal to creation time of the file would solve the purpose.

Rachel 2009-12-20 20:11:06

each one is valid for a particular date, so you would want the ctime stored in the database (or if the file contains the expiration date explicitly, then you can rely on that instead). you can also add the file name into the database, so your records might look like this: (date, file, offer1) (date, file, offer2)select count(*), file from offer where date = '2009-12-20' group by file

jspcal 2009-12-20 20:32:59

ansaurus

tags:

views:

answers:

How to store and query data into Database depending upon its arrival time ?

related questions