CODEWITHSUNDEEP
phpmysql
Code_Ed_Student
Code_Ed_Student

Reputation: 1190

Deleting records older than a month/30 days

I have data stored in mysql database. I am trying to delete all records that are greater than 30 days. All records have a field that specify a created_date of the record. The format of the created_date is Mon, 08 Dec 2014 00:33:13 -0500. When running the delete query, all record are deleted. Even records that are not older than 30 days. I am not sure if it is due to how the date is formatted in the created_date field. How can I delete records older than 30 days?

try {
     $days = strtotime('1 month');
     $delete = $pdo->prepare("DELETE FROM archive WHERE created_date > :days");
            return $delete->execute(array('days' => $days));
        }catch (Exception $e) {
                      echo "Failed: " . $e->getMessage();
        }

Table schema

+------------+-----------+-----+---------------+---------------------------------+
| First Name | Last Name | Age | Date of Birth |           created_date          |
+------------+-----------+-----+---------------+---------------------------------+
| Lionel     | Messi     |  27 | 6/24/1987     | Mon, 08 Dec 2014 00:33:13 -0500 |
| Michael    | Jordan    |  51 | 2/17/1963     | Tue, 15 Nov 2014 00:33:13 -0500 |
| Lebron     | James     |  30 | 12/30/1984    | Fri, 02 Oct 2014 00:33:13 -0500 |
+------------+-----------+-----+---------------+---------------------------------+

Upvotes: 0

Views: 3785

Answers (2)

dchesterton
dchesterton

Reputation: 2110

DELETE FROM archive
WHERE STR_TO_DATE(SUBSTR(created_date, 0, 25), '%a, %d %b %Y %H:%i:%S') <
      DATE_SUB(NOW(), INTERVAL 30 DAY);

You can then remove the PHP code for calculating the date range and let MySQL handle it for you.

If you have control over the database structure, it will be much more efficient to store the date in a DATETIME field, rather than calling STR_TO_DATE on every database row.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Your problem is that the create_date column is a string and not a date. You can convert it to a date using str_to_date():

delete from archive
    where str_to_date(substr(created_date, 6), '%d %b %Y') > :days;

Upvotes: 0

Related Questions