I have a script that I use to create another script to drop temporary
tables that the applications is not taking care of, but I noticed that
when the update_time column is NULL the script is not working
properly...

mysql -p information_schema -s -e "select concat('drop table ', trim
(table_schema),'.',rtrim(table_name),';'),left(cre ate_time,10)
from information_schema.tables where create_time < SUBDATE(CURDATE(),
INTERVAL 7 DAY)
and table_name like 'ruletable%'or table_name like 'temptable%'; " > /
tmp/t.sql

when I get the /tmp/t.sql file I get the following:

drop table
uptilt_db.ruletable_pp_web1test_10765_1243383842_3 813282_prevline;
2009-05-26
drop table
uptilt_db.ruletable_pp_web1test_10765_1243383842_3 813282_prevval;
2009-05-26
drop table
uptilt_db.temptable2_mail1test_0_12464928754189216 ;
2009-07-01
drop table
uptilt_db.temptable3_mail1test_0_12464928752861763 ;
2009-07-01
drop table
uptilt_db.temptable_supression_12465660084700031;
2009-07-02

As you can see the last 3 records should not be there, since I am
trying to get the tables with "<" (less than July 1st, not including
July 1st)

mysql> select SUBDATE(CURDATE(), INTERVAL 7 DAY);
+------------------------------------+
1 row in set (0.00 sec)

Note: the left(create_time) was added to show the create and update
time only!!
But for some reason, perhaps the where clause is not really taking
care of the the dates.

Please advice, thanks in advance.

argentin wrote:

NULL is no value. It is not greater than, less than or equal any value
(including NULL). If you want to test for NULL, you have to test for IS
NULL (or IS NOT NULL for non-NULL values).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

argentin <UnArgentino (AT) gmail (DOT) com> wrote:
This makes no sense. Temporary tables are session-scoped objects.
If your application has temporary tables in open sessions, this
cannot be seen from other sessions.

Maybe you're not talking about "real" temporary tables, but about
normal SQL tables?

I don't see you using the `update_time` column below...

Further more:

- there is no need to trim the table_schema or table_name columns

- you should not use LEFT() on the create_time column; create_time
is a DATETIME, better use DATE_FORMAT() or DATE() to extract the
DATE part

- the comparison create_time < SUBDATE(CURDATE(), INTERVAL 7 DAY)
has different types left/right from the < operator. MySQL thus
extends the right side to DATETIME (using time 00:00:00) and
compares as DATETIME. This might give an unexpected result
(though not in this case).

....

This is strange. Works perfectly for me:

mysql>select table_schema, table_name, create_time
-> from information_schema.tables
-> where table_schema='test'
-> and create_time<SUBDATE(CURDATE(), INTERVAL 21 DAY);

+--------------+------------+---------------------+

mysql>select table_schema, table_name, create_time
-> from information_schema.tables
-> where table_schema='test'
-> and create_time<SUBDATE(CURDATE(), INTERVAL 22 DAY);

+--------------+------------+---------------------+

mysql>select version();

+-----------+


XL

On Jul 6, 11:00*pm, Axel Schwenke <axel.schwe... (AT) gmx (DOT) de> wrote:
Axel,

Yes, I did not put my original script, while I was creating this
message, I tested some other changes, and took out the update_time
form the script.

Here is the original script I was testing:

mysql -p information_schema -s -e "select concat('drop table ',trim
(table_schema),'.',rtrim(table_name),';') from
information_schema.tables where left(update_time,10) < '2009-07-01'
and table_name like 'ruletable%'or table_name like
'temptable%';" > /tmp/t.sql

Then I started working with the subdate curdate, etc.

But my original problem was that if I compared just for update_time,
and found the column was actually NULL, the command did not work. I
was trying to use the IFNULL(), but did not know how to use it on the
where clause. I google it but found most of the examples talking
about using it on the select ...from, instead of the where clause.

But I think I got it all figure it out now:

I use the example from your mail, and got the following results:
USING update_time which will return NULL values, as well as dates.

$ mysql -p information_schema -s -e "select concat('drop table ',trim
(table_schema),'.',rtrim(table_name),';'), create_time, update_time
from information_schema.tables where (table_name like 'ruletable%'or
table_name like 'temptable%') and IFNULL(update_time,'2009-01-01
00:00:00') < SUBDATE(CURDATE(), INTERVAL 7 DAY);"
Enter password:

concat('drop table ',trim(table_schema),'.',rtrim
(table_name),';') create_time update_time
drop table
uptilt_db.ruletable_pp_mail1elabs3_154556_12187397 43_5624962_supression;
2009-05-31 15:50:00 NULL
drop table
uptilt_db.ruletable_pp_mail2elabs3_104626_12248784 61_8193274_supression;
2009-05-31 15:50:00 NULL
drop table
uptilt_db.ruletable_pp_mail2elabs3_136707_12222706 21_6045711_supression;
2009-05-31 15:50:00 NULL
drop table
uptilt_db.ruletable_pp_mail2elabs3_161699_12442116 62_6117572_supression;
2009-06-05 07:21:01 NULL

And using the create_time column:

$ mysql -p information_schema -s -e "select concat('drop table ',trim
(table_schema),'.',rtrim(table_name),';'), create_time, update_time
from information_schema.tables where (table_name like 'ruletable%'or
table_name like 'temptable%') and create_time < SUBDATE(CURDATE(),
INTERVAL 7 DAY);"
Enter password:

concat('drop table ',trim(table_schema),'.',rtrim
(table_name),';') create_time update_time
drop table
uptilt_db.ruletable_pp_mail1elabs3_154556_12187397 43_5624962_supression;
2009-05-31 15:50:00 NULL
drop table
uptilt_db.ruletable_pp_mail2elabs3_104626_12248784 61_8193274_supression;
2009-05-31 15:50:00 NULL
drop table
uptilt_db.ruletable_pp_mail2elabs3_136707_12222706 21_6045711_supression;
2009-05-31 15:50:00 NULL
drop table
uptilt_db.ruletable_pp_mail2elabs3_161699_12442116 62_6117572_supression;
2009-06-05 07:21:01 NULL

Thanks everyone for replying and helping, now I got the right
statement.

Regards, Carlos J.