dates and messages

[asolell]

Hi all,

In a large database called currentdb there is a table called ibf_posts which is 600MB in size. (for those of you who recognize it, it is the InvisionBoard forum table).

There is a column in ibf_posts called ibf_posts.Post_date and as the name imply it is the posting date.
We want to move all the posts which are older than 21 days to a different REPLICA database called archivedb.

To do that, we first do a

mysqldump -uusername -ppassword currentdb >mydump.txt

We then do:

mysql -uusername -ppassword archivedb < mydump.txt

We now have two identical databases.

We now want to remove everything that is 21 days and older from the currentdb database ibf_posts.Post_date.

THIS IS WHERE I GOT STUCK!

delete from ibf_posts where (Post_date - now()) < 21246060

Is that how this scripts should be written? I think I got my logic here incorrect.
Could use some insight.

Thanks,

-Alon.

[devinemke]

$twenty_one_days_ago = date('Y-m-d h:i:s', time() - 1814400);
$result = mysql_query("DELETE FROM ibf_posts WHERE Post_date < '$twenty_one_days_ago'");

[tsinka]

Hi,

shouldn't that rather be something like:

delete * from ibf_posts where (now()-Post_date) > 1814400

What type is the Post_date column of ? You might need to use a slightly different query depending on the type of the column.

[asolell]

What am I missing?

delete * from ibf_posts where (now()-Post_date) > 1814400

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '* from ibf_posts where (now()-Post_date) > 1814400' at line 1

Here is the desc of the table:

mysql> desc ibf_posts
-> ;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| pid | int(10) | | PRI | NULL | auto_increment |
| append_edit | tinyint(1) | YES | | 0 | |
| edit_time | int(10) | YES | | NULL | |
| author_id | mediumint(8) | | MUL | 0 | |
| author_name | varchar(32) | YES | | NULL | |
| use_sig | tinyint(1) | | | 0 | |
| use_emo | tinyint(1) | | | 0 | |
| ip_address | varchar(16) | | | | |
| post_date | int(10) | YES | | NULL | |
| icon_id | smallint(3) | YES | | NULL | |
| post | text | YES | MUL | NULL | |
| queued | tinyint(1) | YES | | NULL | |
| topic_id | int(10) | | MUL | 0 | |
| forum_id | smallint(5) | | MUL | 0 | |
| attach_id | varchar(64) | YES | | NULL | |
| attach_hits | int(10) | YES | | NULL | |
| attach_type | varchar(128) | YES | | NULL | |
| attach_file | varchar(255) | YES | | NULL | |
| post_title | varchar(255) | YES | | NULL | |
| new_topic | tinyint(1) | YES | | 0 | |
| edit_name | varchar(255) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
21 rows in set (0.00 sec)

I'm using:

mysqladmin Ver 8.40 Distrib 4.0.20a, for Win95/Win98 on i32
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 4.0.20a-nt
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 14 min 28 sec

Threads: 4 Questions: 26813 Slow queries: 10 Opens: 746 Flush tables: 1 Open tables: 64 Queries per second avg: 30.891

Does this give any indication as to what is my error?

Thanks,

-Alon.

[tsinka]

Hi,

replace

DELETE * FROM
by
DELETE FROM

Thomas

[asolell]

Yup!
That did that did the trick!
Thanks to all of you.

-Alon.