devslashneil has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I was just wondering what the best way to delete all records from a mySQL database where the S_Date field (of type date) is equal or greater to 1 month from the current date.

I am using the following code: 
my $date = `date -u +%Y-%m-%d`;
chomp $date;

[download]
To get the current date in the correct mySQL DATE format.

I am not sure if it can be done simply using a mySQL statement or if i need to code a way to work out the difference between the two dates and then construct an SQL statement to delete each date.

Thanks a lot.

Neil Archibald
- /dev/IT -

Replies are listed 'Best First'.
Re: Working with mySQL date's
by Abstraction (Friar) on Jul 03, 2003 at 03:28 UTC
    MySQL Manual | 6.3.4 Date and Time Functions 

    mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2003-07-02 22:24:43 |
+---------------------+
1 row in set (0.00 sec)


mysql> SELECT NOW() + INTERVAL 1 MONTH;
+--------------------------+
| NOW() + INTERVAL 1 MONTH |
+--------------------------+
| 2003-08-02 22:25:21      |
+--------------------------+
1 row in set (0.00 sec)

    [download]
    I'll leave the rest up to you...
[reply]
[d/l]
      Wow, thanks a lot for your replies, that really helps :-)

      Neil Archibald
      - /dev/IT -
[reply]
Re: Working with mySQL date's
by atcroft (Abbot) on Jul 03, 2003 at 03:25 UTC

    Probably will be easier to do just using functions in mySQL, with less brain damage. As an example, I found this in the mySQL online documentation, which may suggest a course for you (first example on that page): 

    mysql> SELECT something FROM tbl_name
           WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

    [download]

    Using that as a basis, you could do something like: 

    mysql> DELETE FROM tbl_name 
           WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) > 31;

    [download]

    Hope that helps. (The use of that code from a perl script is left as an exercise for the reader. :)

[reply]
[d/l]
[select]
      I find myself using unix timestamps to store dates in unsigned ints instead of the MySQL date type. It has less trouble with timezone of the db vs timezone of the web server. Besides, to display a localized format and timezome I need to convert through a unix timestamp anyway. Intervals are also easier.

      WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(date_col) > 31*86400;

[reply]

Back to Seekers of Perl Wisdom