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

Selecting previous hour records in mysql

I am trying to select post which are added on last hour from current time. I have explored previous questions but they are using fixed date or datetime. To achieve this, I am using following query:

SELECT * FROM `wp_posts` 
WHERE post_date >= SUBTIME(CURRENT_TIMESTAMP(), "1:0:0") 
and post_date <= CURRENT_TIMESTAMP();

This query is not displaying any result. When when I am using it without AND it just working perfect. So, How to select previous hour records using current datimetime as I am using CURRENT_TIMESTAMP() for this?

Tags:

2

Answers


  1. 0 
     create table wp_posts(
         id int, 
         post_date datetime);

insert into wp_posts(id, post_date)
      values(1, '2023-04-20 08:15:00'),
      (2, '2023-04-21 18:5:00'),
      (3, '2023-04-21 18:1:00'),
      (4, '2023-04-21 18:16:00'),
      (5, '2023-04-21 18:25:00'),
      (6, '2023-04-20 18:30:00');

    select current_timestamp
    current_timestamp
    2023-04-20 18:40:39

    Query:

    SELECT * FROM `wp_posts` 
WHERE   post_date > now() - interval 1 hour;

    Output:

    id post_date
    2 2023-04-21 18:05:00
    3 2023-04-21 18:01:00
    4 2023-04-21 18:16:00
    5 2023-04-21 18:25:00
    6 2023-04-20 18:30:00

    fiddle

    Login or Signup to reply.
  2. 0

    I have tested your code, and it works fine for me. If no row is displayed, it means that there is no post_date between CURRENT_TIMESTAMP() and 1 hour ago.

    On the other hand, if we assume that post_date is always going to be less than your CURRENT_TIMESTAMP()( i do not know if it is your case; you didn’t specify), as a suggestion, you could improve your code by just simply doing this.

    SELECT * FROM `wp_posts` 
WHERE post_date >= SUBTIME(CURRENT_TIMESTAMP(), "1:0:0")

    If post_date is always less than CURRENT_TIMESTAMP(), there is no need to include it.

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

Back To Top
Search