cornercuttin
Programmer
ok. i have been sitting here looking at this for a few hours thinking about it, and i have no idea where to go.
the situation is as follows: i have this huge table that gets updates throughout the day, and in this database there are fields for year, month, day, hour, second for when the database insert takes place (i know. using a simple "date" field would have been useful, but I didn't create the database and we have too much software depending on it to change it).
so for each of these rows in table, there are the date fields and a few other fields. lets call this table X. now i need to be able to get some statistics out of the data in this field. but the problem is that this table is about 9 million rows, so trying to query over 9 million rows every hour or whenever is going to take way too long.
the stats that i need are to be displayed on a web page. basically, this web page will hold the stats from day 0 all the way up to the previous day (today - 1). so i made a table to keep track of these running stats so that they can easily be called and viewed. Lets call this table Y.
my problem is this: every day, i need to go through and collect the stats for the previous day and update my stats table. this is easy enough if the world is perfect, but if something big happens, and lets say the network goes down or something, then i may need to get the data for the previous 2 or 3 days. now i will have a table that will hold a "last run time", so that i will know what day i last collected stats for. this is table Z.
how can i create a stored procedure or something to say "collect stats for every hour starting at midnight last night until the last time you collected stats"? another way: how can i get the "last run time" from table Z and collect the stats from table X that were collected between yesterday and the day specified in table Z and put those stats in table Y?
how does one iterate through days. I understand that i can do some kind of conversion of the date, but beyond that, i am stuck. how would i iterate through days like this?
thanks for any help...
the situation is as follows: i have this huge table that gets updates throughout the day, and in this database there are fields for year, month, day, hour, second for when the database insert takes place (i know. using a simple "date" field would have been useful, but I didn't create the database and we have too much software depending on it to change it).
so for each of these rows in table, there are the date fields and a few other fields. lets call this table X. now i need to be able to get some statistics out of the data in this field. but the problem is that this table is about 9 million rows, so trying to query over 9 million rows every hour or whenever is going to take way too long.
the stats that i need are to be displayed on a web page. basically, this web page will hold the stats from day 0 all the way up to the previous day (today - 1). so i made a table to keep track of these running stats so that they can easily be called and viewed. Lets call this table Y.
my problem is this: every day, i need to go through and collect the stats for the previous day and update my stats table. this is easy enough if the world is perfect, but if something big happens, and lets say the network goes down or something, then i may need to get the data for the previous 2 or 3 days. now i will have a table that will hold a "last run time", so that i will know what day i last collected stats for. this is table Z.
how can i create a stored procedure or something to say "collect stats for every hour starting at midnight last night until the last time you collected stats"? another way: how can i get the "last run time" from table Z and collect the stats from table X that were collected between yesterday and the day specified in table Z and put those stats in table Y?
how does one iterate through days. I understand that i can do some kind of conversion of the date, but beyond that, i am stuck. how would i iterate through days like this?
thanks for any help...