27

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.

miken32
  • 42,008
  • 16
  • 111
  • 154
eComEvo
  • 11,669
  • 26
  • 89
  • 145

8 Answers8

85

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.

miken32
  • 42,008
  • 16
  • 111
  • 154
  • Oddly, that returns records only from today instead of yesterday. – eComEvo Jan 21 '16 at 00:48
  • `SELECT @@global.time_zone, @@session.time_zone;` just gives me `SYSTEM` for both values. When I check the TZ on the database system, it is UTC whereas mine is EST. The query will ultimately only be run from a script on the remote system, so I suppose this should be a non-issue. – eComEvo Jan 21 '16 at 00:59
  • That would explain it. UTC is currently 1 AM tomorrow to us North Americans. – miken32 Jan 21 '16 at 01:00
  • Tried changing it to this to account for the TZ, but now it gets records from today as well even when I execute it directly on the remote system. Any ideas? `DATE(CONVERT_TZ(created_at, '+00:00', '-05:00')) = DATE(CONVERT_TZ(NOW(), '+00:00', '-05:00') - INTERVAL 1 DAY)` – eComEvo Jan 21 '16 at 01:11
  • If you are on the remote system, there's no need to change time zones. Just use the query as in the answer. Or try `SELECT DATE(NOW() - INTERVAL 1 DAY)` if you don't believe me ;) – miken32 Jan 21 '16 at 01:13
  • Tried that, didn't work. However, this did: `DATE(created_at) = DATE(CONVERT_TZ(NOW(), '+00:00', '-05:00') - INTERVAL 1 DAY)` – eComEvo Jan 21 '16 at 01:18
11

You can still use the index if you say 

SELECT * FROM TABLE
WHERE CREATED_AT >= CURDATE() - INTERVAL 1 DAY
  AND CREATED_AT < CURDATE();
Alan Hadsell
  • 470
  • 2
  • 7
3

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)
Anthony
  • 36,459
  • 25
  • 97
  • 163
2

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() )
Community
  • 1
  • 1
Wilnar Vincent
  • 72
  • 13
1

use: subdate(current_date, 1)

it's awesome for your case!

pivanchy
  • 723
  • 3
  • 15
  • This doesn't give me any results: `select * from my_table where created_at = subdate(current_date, 1);` How did you mean for this to be used in a query? – eComEvo Jan 21 '16 at 00:42
  • `select * from my_table where created_at > subdate(current_date, 1);` – pivanchy Jan 21 '16 at 00:45
  • `select * from my_table where created_at between subdate(current_date, 1) and current_date;` this query will fetch all the data from 00:00:00 from yesterday till 00:00:00 for today(I mean 24 hours), because previous query will return all the data from yesterday till current time – pivanchy Jan 21 '16 at 00:55
0

SELECT subdate(current_date(), 1)

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

Venkatesh Chakilam
  • 1
  • Hello Venkatesh and welcome to StackOverflow! Please use code snippets for your sql commands and a bit of explanation. This makes it easier readable and understandable ;) – Timo Reymann Dec 02 '20 at 22:15
  • While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Kevin M. Mansour Aug 22 '21 at 02:16
0

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;
iminiki
  • 2,549
  • 12
  • 35
  • 45
Prathviraj Singh Chouhan
  • 43
  • 1
  • 8
0

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())
louigi600
  • 716
  • 6
  • 16