SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs down Deleting Old Data from MYSQL Table, Please Help

    Hello all. I have a few questions that I hope you can help me out on.

    I have a table called 'transaction' on a MYSQL database. Within this table there is a field called 'SettlementDate' the format of the date is in 0101, 0102, 0103 (Jan 1, Jan 2, Jan 3) format. I need to create a cronjob to delete all data in 'transaction' older than three months.

    Currently, I have

    delete *
    from transaction
    where SettlementDate > '3 month'

    and it returns an empty field.

    I'm a little stuck and I'd appreciate some help.

    For the cronjob I was going to do:

    --------------------------------------------------------------------

    mysql --user=+++++++ --password=+++++++
    use +++++++
    delete * from transaction where SettlementDate > '3 month';

    --------------------------------------------------------------------

    Thanks!

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you store your dates as an actual date type, instead of what looks like some character type like varchar, then you can use tons of well tested built in functionality that your database provides for manipulating dates. For example
    Code:
    WHERE SettlementDate < CURRENT_DATE - INTERVAL 3 MONTH
    If you can't change your table so that column is a date type, then you'll need a more complicated solution, which can get real ugly when the dates start to span into different years, because you currently aren't storing the year.

    How many months back does the data currently go?

  3. #3
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I know that were they to be in a standard format, my life would be significantly easier, but I'm stuck with this. The data goes back To 12/01 of last year.

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    maybe
    Code:
    WHERE CONCAT(EXTRACT(YEAR FROM CURRENT_DATE), '/', SettlementDate) < CURRENT_DATE - INTERVAL 3 MONTH
       OR CONCAT(EXTRACT(YEAR FROM CURRENT_DATE), '/', SettlementDate) > CURRENT_DATE + INTERVAL 1 DAY
    dates from the previous year will end up being "in the future" if we tack the year onto it. So that's why I used the second condition, because I assume you will never lets the db build up with more than a few months of data. I added one day to the future check in case there's some blip due to changing timezones or dst etc...

  5. #5
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I input this into the script, and change delete to select, it just returns the entire database. Any more ideas?

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    remove '/', from the concats.


Bookmarks

Posting Permissions

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