Menu
What is the most efficient way to get all records with a datetime field that falls somewhere between yesterday at 00:00:00 and yesterday at 23:59:59?
Table:
id created_at 1 2016-01-19 20:03:00 2 2016-01-19 11:12:05 3 2016-01-20 03:04:01 Suppose yesterday was 2016-01-19, then in this case all I'd want to return is rows 1 and 2.
467
To get yesterday's date, you need to subtract one day from today's date. Use CURDATE() to get today's date. In MySQL, you can subtract any date interval using the DATE_SUB() function. Here, since you need to subtract one day, you use DATE_SUB(CURDATE(), INTERVAL 1 DAY) to get yesterday's date.
To get yesterday's date, you need to subtract one day from today's date. Use GETDATE() to get today's date (the type is datetime ) and cast it to date . In SQL Server, you can subtract or add any number of days using the DATEADD() function. The DATEADD() function takes three arguments: datepart , number , and date .
To get the last record, the following is the query. mysql> select *from getLastRecord ORDER BY id DESC LIMIT 1; The following is the output. The above output shows that we have fetched the last record, with Id 4 and Name Carol.
Here's the SQL query to get records from last 7 days in MySQL. In the above query we select those records where order_date falls after a past interval of 7 days. We use system function now() to get the latest datetime value, and INTERVAL clause to calculate a date 7 days in the past.
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.
50
You can still use the index if you say
SELECT * FROM TABLE WHERE CREATED_AT >= CURDATE() - INTERVAL 1 DAY AND CREATED_AT < CURDATE(); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
