Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jul 2005
    Location
    LA, California
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete previous entries after a date?

    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]:
    Code:
    ('1307736601', '14.94'),
    ('1307736615', '15.03'),
    ('1307736610', '15.03')
    ('1307736631', '15.17'),
    ('1307736646', '15.11'),
    here it is explained
    Code:
    ('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.
    PHP Code:
    $time time();
    $past time() - (24 60 60);
    $sql mysql_query("DELETE FROM `stocks` WHERE `date` > '$past'........This is where I get lost;"); 
    Thanks!
    -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    There are 3 kinds of ppl those who can count and those who cant
    -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    Script-Megafest.com Coming Oh so very soon

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    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
    Code:
    ('1307736601', '14.94'),
    ('1307736615', '15.03'),
    ('1307736610', '15.03')
    ...
    I *HOPE* it is true that it actually looks like
    Code:
    (1307736601, 14.94),
    (1307736615, 15.03),
    (1307736610, 15.03)
    ...
    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:
    Code:
    DELETE FROM `stocks` WHERE DATE( FROM_UNIXTIME(`date`) ) < CURDATE()
    You might want to test my theory by first doing something like
    Code:
    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.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    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:
    Code:
    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.

  • #4
    Regular Coder
    Join Date
    Jul 2005
    Location
    LA, California
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    There are 3 kinds of ppl those who can count and those who cant
    -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    Script-Megafest.com Coming Oh so very soon

  • #5
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    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.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link


  •  

    Posting Permissions

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