dates and msgs - round two

[asolell]

Hi Y'all,

I'm having a bit of a confusion:

delete from ibf_posts where (Post_date - now()) < 212460*60
or
delete from ibf_posts where (now()-Post_date) > 1814400

Shouldn't the answer be more like this:

3 weeks ago's date = Threshold = (now() - 1814400)

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

I'm always good at messing the dates limit and now I'm kinda lost:

Which one of the two (or they may even produce the same result) will keep my msgs that only 21 days old and remove anything that is Older than 21 days?

When I ran:

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

I ran it and was confused because I think it earased more than I expected,..or I maybe just confused.

Can someone please verify what am I left with after running this?

Thanks,

-Alon.

[devinemke]

what field type is Post_date?

[asolell]

+-------------+--------------+------+-----+---------+----------------+
| 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

[devinemke]

i'll assume from your data structure that post_date is a unix timestamp.

DELETE FROM ibf_posts WHERE post_date < UNIX_TIMESTAMP() - 1814400

[asolell]

I'm using a Windows Server 2003 box.
Will that run there?

DELETE FROM ibf_posts WHERE post_date < UNIX_TIMESTAMP() - 1814400

-Alon.

[asolell]

You are a life saver!

Finally I can some sleep 🙂.

Thanks a lot.

-Alon.