mysql help

Anon

I am trying to write a query that would return the following: all items that are new today and all items ending today.

Whats would be the optimal mysql query for a large recordset.

select * from item_tbl where now() -creation_date <= 24

select * from item_tbl where (close_date - now()) <= 24

cdhogan

Jay,

Are item_tbl.creation_date and itm_table.close_date date fields or datetime fields?

If the fields are just date fields then you can use:
select from item_tbl where creation_date = now()
select from item_tbl where close_date = now()

If the fields are datetime fields use:
select * from item_tbl where DAYOFYEAR(creation_date) = DAYOFYEAR(now()) ;

select * from item_tbl where DAYOFYEAR(close_date) = DAYOFYEAR(now());

Using the date_sub() function could give erroneous results depending on the time run and time value in the field if you are only looking for todays date

Date sub function looks like:
DATE_SUB(now() interval 24 HOURS)

The MySQL documentation gives a complete description of the DATE_SUB() function, as well as many others, and can be downloaded at no charge from http://www.mysql.com/documentation/index.html.

Charles

cdhogan

Jay,

PS, something I just thought of, if the database spans multiple years and they are datetime fields, you will also want to check the year by appending the following:

and YEAR( ) = YEAR(now());
being whichever field you are looking for in the
query

Charles

Anon

they are timestamp(14) fields, the method you are talking about might be slow for lard size db each row need to do this calculation..

thanks for your response

cdhogan

Possibly, another way to run the query for todays date only is:
select * from item_tbl where
DATE_FORMAT( creation_date, '%Y-%m-%d') = CURDATE();

That removes a couple of functions.

Charles

[deleted]

Nope, that just replaces the 'dayofyear' with the much more complex 'date_format'

If the fields are all timestamps, simply create the timestamp that you are looking for, and compare the values directly:

SELECT * FROM table WHERE creation_date > timestamp_of_24_hrs_ago;

and

SELECT * FROM table WHERE close_date > timestamp_of_last_midnight;