CODEWITHSUNDEEP
mysqlsqldatetime
eComEvo
eComEvo

Reputation: 12601

All MySQL records from yesterday

What is an efficient way to get all records with a datetime column whose value falls somewhere between yesterday at 00:00:00 and yesterday at 23:59:59?

SQL:

CREATE TABLE `mytable` (
  `id` BIGINT,
  `created_at` DATETIME
);

INSERT INTO `mytable` (`id`, `created_at`) VALUES
  (1, '2016-01-18 14:28:59'),
  (2, '2016-01-19 20:03:00'),
  (3, '2016-01-19 11:12:05'),
  (4, '2016-01-20 03:04:01');

If I run this query at any time on 2016-01-20, then all I'd want to return is rows 2 and 3.

Upvotes: 27

Views: 62699

Answers (8)

louigi600
louigi600

Reputation: 743

To really get yesterdays data no matter at what time you run the query I use this the timestamp on a specific date:

select timestamp(DATE_SUB(CURDATE(), INTERVAL 1 DAY)), timestamp(CURDATE());

So the query would look like this:

select * from (TABLENAME) where  created_at between timestamp(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) and timestamp(CURDATE())

Upvotes: 0

Prathviraj Singh Chouhan
Prathviraj Singh Chouhan

Reputation: 41

You can use this, just put tablename and columnName (Which Contain 2021/01/09 or 2022-01-11 14:56:07 etc)

select * from (TABLENAME) where DATE(columnNAME) = TODAY - 1;

Upvotes: 0

Venkatesh Chakilam
Venkatesh Chakilam

Reputation: 1

SELECT subdate(current_date(), 1)

SELECT * FROM table WHERE created_at >= subdate(current_date(), 1)

Upvotes: 0

miken32
miken32

Reputation: 42766

Since you're only looking for the date portion, you can compare those easily using MySQL's DATE() function.

SELECT * FROM table WHERE DATE(created_at) = DATE(NOW() - INTERVAL 1 DAY);

Note that if you have a very large number of records this can be inefficient; indexing advantages are lost with the derived value of DATE(). In that case, you can use this query:

SELECT * FROM table
    WHERE created_at BETWEEN CURDATE() - INTERVAL 1 DAY
        AND CURDATE() - INTERVAL 1 SECOND;

This works because date values such as the one returned by CURDATE() are assumed to have a timestamp of 00:00:00. The index can still be used because the date column's value is not being transformed at all.

Upvotes: 87

Wilnar Vincent
Wilnar Vincent

Reputation: 82

Here is the same question with an answer. To summarize answer for you, use subdate() as suggested by Sajmon.

subdate(currentDate, 1)

using your table it should be.

select *
from tablename
where created_at between subdate(CURDATE(), 1)
and date (now() )

Upvotes: 2

Anthony
Anthony

Reputation: 37085

You can use subdate to indicate "yesterday" and use date() to indicate that you want records where just the date part of the column matches. So:

SELECT *
FROM tablename
WHERE DATE(created_at) = SUBDATE(CURRENT_DATE(), INTERVAL 1 DAY)

Upvotes: 3

Alan Hadsell
Alan Hadsell

Reputation: 470

You can still use the index if you say 

SELECT * FROM TABLE
WHERE CREATED_AT >= CURDATE() - INTERVAL 1 DAY
  AND CREATED_AT < CURDATE();

Upvotes: 11

pivanchy
pivanchy

Reputation: 723

use: subdate(current_date, 1)

it's awesome for your case!

Upvotes: 1

Related Questions