Previous date

jacobelias

Hello,

Using a single query how can I get previous day's values?
In SQL Server
select * from table where dates_dt-1 will work. In My SQL how to get write this query?

Thanks
Jac

tekky

SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS yesterday

would return the date of yesterday....

jacobelias

SELECT DATE_SUB(indexdate, INTERVAL 1 DAY) AS yesterday from indexvalues
order by indexdate desc limit 1

with this query I got previous date, is it possible to include where condition so that I can retrieve values based on previous date?

Thanks

Jac

tekky

SELECT * from indexvalues WHERE somecomaparisonvalue >= DATE_SUB(indexdate, INTERVAL 1 DAY)
order by indexdate desc limit 1

jacobelias

select * from indexvalues where date_sub(indexdate, INTERVAL 1 day)
order by indexdate desc limit 1

I have query like above,it didn't fetch values for previous day, in fact for today. Some thing wrong with my query?

Thanks

Jac

tekky

woops

SELECT * from indexvalues WHERE indexdate = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
order by indexdate desc limit 1

that would grab YESTERDAY from the table if it exists in there

jacobelias

Unfortunately it didn't fetch anything

I tried with this query as well, no results though

SELECT * from indexvalues
WHERE indexdate = DATE_SUB(indexdate, INTERVAL 1 DAY)
order by indexdate desc limit 1

Any comments

Thanks

Jac

jacobelias

Is there any function for getting max date instead of CURRENT_DATE. So that it will fetch one date prior to max date

Thanks

Jac

tekky

Originally posted by tekky
woops

SELECT * from indexvalues WHERE indexdate = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
order by indexdate desc limit 1

that would grab YESTERDAY from the table if it exists in there

if thats the case then you have no entries on that date... I just did this...

SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY)

and got 2003-11-02 (obviously its the 11/03/2003 right now...

nnichols

It all depends on the type of the column that you are using for the comparison.

You can use -

SELECT * 
FROM tbl_name
WHERE date_col = (CURRENT_DATE - INTERVAL 1 DAY)

for comparisons with a DATE column but it will not give the desired results with DATETIME or TIMESTAMP columns. The following will work with any MySQL date column (DATE, DATETIME, TIMESTAMP) but it is very inefficient as it will result in a full table scan to produce the results.

SELECT *
FROM tbl_name
WHERE DATE_FORMAT(date_col, '%Y-%m-%d') = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)

This next example looks more is more efficient as it enables the use of indexes -

SELECT *
FROM tbl_name
WHERE date_col >= DATE_SUB(CURRENT_DATE, INTERVAL '24:00:00' HOUR_SECOND)
AND date_col <= DATE_SUB(CURRENT_DATE, INTERVAL 1 SECOND)

Hope this helps