* Ashley M. Kirchner
> I have a table that has a datetime field that gets set every time
> data gets entered in the table (for example "2004-05-16 19:08:59"). How
> can I select all entries that were entered one year ago today. And, how
> can I select entries that were entered one year ago today +7 days.
>
> So, if today being 2004-05-16,
> -> I want to select all entries dated 2003-05-16
> -> And I also want entries dated between 2003-05-16 and 2003-05-23
Selecting rows based on dates is easy, you just need to know a few functions
and the special time operator called 'interval'. Selecting rows from a
specific date:
SELECT * FROM mytable
WHERE datetimecol BETWEEN '2003-05-16 00:00:00' AND '2003-05-16 23:59:59';
...and of course for the week:
SELECT * FROM mytable
WHERE datetimecol BETWEEN '2003-05-16 00:00:00' AND '2003-05-23 23:59:59';
The next example would also work, but it is slower, because the server must
calculate the date for each row:
SELECT * FROM mytable
WHERE date_format(datetimecol,'%Y-%m-%d') = '2003-05-16';
Often one would want this kind of thing to be dynamic, you can use the
CURDATE() function to get the current date, and the INTERVAL operator is
kind of neat, it makes the query more readable:
SELECT * FROM mytable
WHERE
datetimecol >= (CURDATE() - INTERVAL 1 YEAR) AND
datetimecol < (CURDATE() - INTERVAL 1 YEAR) + INTERVAL 1 DAY;
...and for the full week:
SELECT * FROM mytable
WHERE
datetimecol >= (CURDATE() - INTERVAL 1 YEAR) AND
datetimecol < (CURDATE() - INTERVAL 1 YEAR) + INTERVAL 7 DAY;
There are lots of more date&time functions, take a look at the maual:
<URL: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
