06-10-2011, 09:38 PM
I have a simple stock retaining refresh script that updates a table every few seconds with current ticker prices but i have it set to only update during market hours. The one problem I'm having is I can't for the life of me figure out the last part. I'm looking for a script that when the market is closed(i've done this part in php) to search through the table and delete all entries after the past day BUT leave the latest one
For example this is the table (time, price) [RAW]:
here it is explained
('TODAY 4:00, '14.94'),
('TODAY 1:21', '15.03'),
('TODAY 11:34', '15.03'),
('TUESDAY 4:00', '15.17'),
('MONDAY 4:00', '15.11'),
I need a script that will go through all of the entries in the past day and delete all but the 4:00 entry. I was thinking something like this.
$time = time();
$past = time() - (24 * 60 * 60);
$sql = mysql_query("DELETE FROM `stocks` WHERE `date` > '$past'........This is where I get lost;");
06-10-2011, 09:57 PM
Well, part of the problem is that you for some reason didn't use a DATETIME datatype for your date column.
In any case, I seriously hope that your data does *NOT* look like
I *HOPE* it is true that it actually looks like
That is, I hope those values are actually numbers.
When you put '...' around them, you make them into strings (text fields in the DB) and then it's tough to do numerical computations on them.
It looks to me like those numbers are Linux timestamp values, yes?
So you *can* convert them to DATETIME in MySQL.
Look up the function FROM_UNIXTIME( ) in the MySQL docs.
So now it's not too hard:
DELETE FROM `stocks` WHERE DATE( FROM_UNIXTIME(`date`) ) < CURDATE()
You might want to test my theory by first doing something like
SELECT FROM_UNIXTIME(`date`) AS theDateTime FROM stocks ORDER BY `date` DESC LIMIT 20
See if indeed those timestamps agree with what MySQL thinks they are.
Your choice of the name `date` for your column is a little unfortunate, as the function DATE( ) in MySQL is used to extract the date-only value from a DATETIME value, but so long as you put the back ticks around it (`date`) it won't cause grief.
06-10-2011, 09:59 PM
NOTE: If you have multiple values from today, that DELETE I showed will leave all of them. In only removes records older than today.
If you really only want to keep *ONE* record, then the easy way is this:
DELETE FROM stocks WHERE `date` < ( SELECT MAX(`date`) FROM stocks )
If you need to do this on a pre-stock basis, then show more of your table design. Yes, it can be done all in MySQL.
06-12-2011, 03:02 AM
Old Pedant thanks for the reply! Will what you posted still apply if I'm looking to keep one entry per day. My PHP script enters prices into the database every few seconds and I'm wanting my MySQL query to delete, when the market closes(i've got this done), all the entries that are within the day but leave the latest one for the past 24 hours AND also keep one per day previous. I hope that makes sense. BTW yes my table stores numbers without the quotes
do you mean you want to delete all the current days records except the last one and you want this trend to apply to all days? then use the code Old_Pedant supplied so that you can acheive this from today onwards.
As for removing all but the last entry for days earlier than today, I'll sleep on that til the morning.