Question 19983 in Mysql Question posted in
The official MySQL documentation can be found here.

how to retrieve records from yesterday's date at the exact same hour and minute as the current time – Mysql

I want to retrieve yesterday’s record in an exact hour and minute time

For example, this is today’s date and time 2023-04-22 14:32:15
I want the records in 2023-04-21 14:32:15

I tried some queries but non of them works for me:

SELECT CONCAT(
  TIMESTAMPDIFF(DAY,'2023-04-21 13:45:00', '2023-04-22 13:45:00'));

 SELECT * FROM message 
WHERE datetime BETWEEN CURDATE() - INTERVAL 1 
DAY AND CURDATE() - INTERVAL 1 HOUR;
Tags:

2

Answers


  1. 0

    Suppose in your SQL table you have column ‘date_time’. Then this type of query you can use :

    select * from table_name
where date(date_time)=date(now()-interval 1 day)
and hour (date_time)=hour(now()
and minute(date_time)=minute(now();

    note : please check your database server have same timezone

    Login or Signup to reply.
  2. 0

    Assuming you’ve got a column named date_column with type DateTime you could use this MySQL sentence to retrieve all records from yesterday from your_table

    SELECT *
FROM your_table
WHERE date_column >= DATE_SUB(NOW(), INTERVAL 1 DAY)

    In case date_column is a timestamp field, then use FROM_UNIXTIME() , UNIX_TIMESTAMP() and DATE_FORMAT() accordingly to your needs

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search