 | | | Using MySQL date/time | Using MySQL date/time 2007-10-08 - By Tiago Cruz
Back
Hello guys,
I would like to get some registers on my database that are older than 90
days, and after delete it because the table is very larger:
mysql> select count(*) from max_ecardsent;
+-- ---- --+
| count(*) |
+-- ---- --+
| 1172330 |
+-- ---- --+
1 row in set (0.01 sec)
But I'm confusing when I use the "FROM_UNIXTIME", the output is very
confusable, like:
mysql> SELECT FROM_UNIXTIME(cs_date_create),FROM_UNIXTIME(cs_date_delete) \
FROM max_ecardsent ORDER BY cs_date_delete ASC LIMIT 5;
+-- ---- ---- ---- ---- ---- ---+-- ---- ---- ---- ---- ---- ---+
| FROM_UNIXTIME(cs_date_create) | FROM_UNIXTIME(cs_date_delete) |
+-- ---- ---- ---- ---- ---- ---+-- ---- ---- ---- ---- ---- ---+
| 2007-03-05 20:16:56 | 2007-03-20 00:00:00 |
| 2007-03-05 21:10:56 | 2007-03-20 00:00:00 |
| 2007-03-05 21:10:56 | 2007-03-20 00:00:00 |
| 2007-03-05 21:10:56 | 2007-03-20 00:00:00 |
| 2007-03-05 21:10:56 | 2007-03-20 00:00:00 |
+-- ---- ---- ---- ---- ---- ---+-- ---- ---- ---- ---- ---- ---+
mysql> SELECT FROM_UNIXTIME(cs_date_create),FROM_UNIXTIME(cs_date_delete) \
FROM max_ecardsent ORDER BY cs_date_delete DESC LIMIT 5;
+-- ---- ---- ---- ---- ---- ---+-- ---- ---- ---- ---- ---- ---+
| FROM_UNIXTIME(cs_date_create) | FROM_UNIXTIME(cs_date_delete) |
+-- ---- ---- ---- ---- ---- ---+-- ---- ---- ---- ---- ---- ---+
| 2006-06-07 23:55:37 | 2010-01-08 02:00:22 |
| 2006-06-07 23:55:37 | 2010-01-08 02:00:22 |
| 2006-06-15 15:16:17 | 2009-07-01 03:00:22 |
| 2006-05-25 18:26:08 | 2009-06-09 03:00:22 |
| 2006-05-25 18:29:59 | 2009-06-09 03:00:22 |
+-- ---- ---- ---- ---- ---- ---+-- ---- ---- ---- ---- ---- ---+
5 rows in set (2.56 sec)
Someone can help me?
Thanks!
mysql> desc max_ecardsent;
+-- ---- ---- ---- ---- -----+-- ---- ------+-- ---+-- --+-- ------+-- ----+
| Field | Type | Null | Key | Default | Extra |
+-- ---- ---- ---- ---- -----+-- ---- ------+-- ---+-- --+-- ------+-- ----+
| cs_id | varchar(20) | NO | PRI | | |
| cs_ec_id | mediumint(8) | NO | MUL | 0 | |
| cs_message | text | YES | | NULL | |
| cs_sent | tinyint(1) | NO | | 0 | |
| cs_send_month | tinyint(2) | NO | | 0 | |
| cs_send_mday | tinyint(2) | NO | | 0 | |
| cs_send_year | mediumint(4) | NO | | 0 | |
| cs_notify | tinyint(1) | NO | | 0 | |
| cs_copy | tinyint(1) | NO | | 0 | |
| cs_fmail | varchar(255) | NO | | | |
| cs_fname | varchar(100) | NO | | | |
| cs_from_email | varchar(255) | NO | | | |
| cs_from_name | varchar(100) | NO | | | |
| cs_date_create | int(11) | NO | MUL | 0 | |
| cs_date_create_server_time | int(11) | NO | | 0 | |
| cs_date_send | int(11) | NO | | 0 | |
| cs_date_delete | int(11) | NO | | 0 | |
| cs_lang | varchar(50) | YES | | NULL | |
| cs_user_name_id | varchar(25) | NO | | | |
| cs_pkdate | int(11) | NO | | 0 | |
| cs_timezone | decimal(5,2) | NO | | 0.00 | |
| cs_music_filename | varchar(100) | YES | | NULL | |
| cs_poem | varchar(100) | YES | | NULL | |
| cs_skin_name | varchar(100) | YES | | NULL | |
| cs_stamp_filename | varchar(100) | YES | | NULL | |
| cs_java | varchar(50) | YES | | NULL | |
| cs_poem_align | varchar(10) | YES | | NULL | |
| cs_sender_ip | varchar(15) | YES | MUL | NULL | |
+-- ---- ---- ---- ---- -----+-- ---- ------+-- ---+-- --+-- ------+-- ----+
28 rows in set (0.01 sec)
--
Tiago Cruz
http://everlinux.com
Linux User #282636
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
|