jaredmtucker — 2010-01-11T13:04:46-05:00 — #1
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
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=+++++++
delete * from transaction where SettlementDate > '3 month';
crmalibu — 2010-01-11T15:55:28-05:00 — #2
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
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?
jaredmtucker — 2010-01-11T17:21:21-05:00 — #3
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.
crmalibu — 2010-01-12T11:34:09-05:00 — #4
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...
jaredmtucker — 2010-01-12T14:08:47-05:00 — #5
When I input this into the script, and change delete to select, it just returns the entire database. Any more ideas?
crmalibu — 2010-01-12T14:36:48-05:00 — #6
remove '/', from the concats.