Hi Scott,
I show you timestamp usage, but there is also datetime and date. you can read
detailled infos at :
http://dev.mysql.com/doc/mysql/en/datetime.html
mysql> create table items(itemRef varchar(10), dat timestamp default
current_timestamp);
Query OK, 0 rows affected (0.20 sec)
mysql>
mysql>
mysql> insert into items (itemRef) values('value 1'),('value 2');
mysql> insert into items (itemRef) values('value 3');
mysql> select * from items;
+---------+---------------------+
| itemRef | dat |
+---------+---------------------+
| value 1 | 2005-05-17 23:55:10 |
| value 2 | 2005-05-17 23:55:10 |
| value 3 | 2005-05-17 23:57:59 |
+---------+---------------------+
3 rows in set (0.00 sec)
mysql> insert into items (itemRef,dat) values('value 4','2005-04-10'),('value
5','2004-02-10'),('value 6','2005-05-18');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from items;
+---------+---------------------+
| itemRef | dat |
+---------+---------------------+
| value 1 | 2005-05-17 23:55:10 |
| value 2 | 2005-05-17 23:55:10 |
| value 3 | 2005-05-17 23:57:59 |
| value 4 | 2005-04-10 00:00:00 |
| value 5 | 2004-02-10 00:00:00 |
| value 6 | 2005-05-18 00:00:00 |
+---------+---------------------+
6 rows in set (0.00 sec)
mysql> select * from items where dat <= date_add(now(),interval -2 day);
+---------+---------------------+
| itemRef | dat |
+---------+---------------------+
| value 4 | 2005-04-10 00:00:00 |
| value 5 | 2004-02-10 00:00:00 |
+---------+---------------------+
2 rows in set (0.00 sec)
mysql> select * from items where dat <= date_add(current_timestamp,interval -10
day);
+---------+---------------------+
| itemRef | dat |
+---------+---------------------+
| value 4 | 2005-04-10 00:00:00 |
| value 5 | 2004-02-10 00:00:00 |
+---------+---------------------+
2 rows in set (0.00 sec)
So you can delete rather than the select above.
Mathias
Selon Scott Purcell <[EMAIL PROTECTED]>:
> Hello,
>
> I would like to do the following: I am creating a site where people can add
> items to a cart. In order to keep items for [X] amount of days, I would like
> to create a database table in mysql to hold a 'itemRef' and a 'Date'. Then in
> a relationship table I can hold the 'itemRef' and 'items' they have choosen.
> I think this would be simple.
>
> But there are a lot of choices for the date field. I would like a date field
> that I can insert a now() or something, when I insert. And then later,
> through Java code, query and find all dates that are greater than [X] amount
> of days, and delete them to keep the database clean?
>
> So my question would be,
>
> A) which date type field should I create.
> B) how to insert now()
> C) can I run one query to find out if the date field is greater than [X]
> days?
>
> Any help would be appreciated.
> Sincerely
> Scott
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
