I want to get rows where date is equal to Previous day,

Table Schema as below,

-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL,
  xYear INTEGER,
  xMonth INTEGER,
  xDay INTEGER,  
);

-- Sample Table ---

---- Expected result ---- considering today is 13th Jan, so i want data for 12th Jan----

not sure , how to achieve this.

CodePudding user response：

It should be quite clear this is a bad DB structure and the date should be stored as date in one single column instead in future.

Creating the desired result can be achieved by building a date out of those three columns and then check this date is yesterday.

There are lots of functions which can do this job, here one way with CAST and CONCAT:

SELECT 
id, name, gender,  
xYear, xMonth, xDay
FROM students
WHERE 
  CAST(CONCAT(xYear,'-',xMonth,'-',xDay) AS DATE) 
    = CURDATE() - INTERVAL 1 DAY;

You can also try above without the CAST and check if this is executed faster:

SELECT 
id, name, gender,  
xYear, xMonth, xDay
FROM students
WHERE 
  CONCAT(xYear,'-',xMonth,'-',xDay) = CURDATE() - INTERVAL 1 DAY;

Try out here: db<>fiddle

CodePudding user response：

You can concat the columns to a stringand convert it to a date and compare it with "yesterday"

select  * 
from students 
where STR_TO_DATE(concat(xYear, '-',xMonth, '-',xDay), "%Y-%m-%d")=DATE_SUB(CURDATE(), INTERVAL 1 DAY) 

Demo