Search code examples
mysqlsqlintervals

grab the data rows from the table which created within last hour with where clause

I have a data table like this

|-----------------------------------------------------------------|
| Id   |  OrderId |  Description   | CreatedAt                    |
|-----------------------------------------------------------------|
| 1    |  001     |  order desc 1  | Sat Apr 30 17:42:00 IST 2022 |
| 2    |  002     |  order desc 2  | Sat Apr 30 17:55:00 IST 2022 |
| 3    |  003     |  order desc 3  | Sat Apr 30 18:04:00 IST 2022 |
-------------------------------------------------------------------

All the columns are varchar/string type. I want a query to grab only those rows which are create in last 1 hour only. I have created one query for this but its return blank result

SELECT Id FROM TABLE WHERE CAST(CreatedAt as datetime) > DATE_SUB(NOW(), INTERVAL 1 HOUR)

I dont know if the query is incorrect or there something i need to modify in it.

Solution

  • It appears that your CreatedAt column is actually text. If you want to convert it to a datetime column, you will first have to use STR_TO_DATE() with an appropriate format mask.

    SELECT Id
FROM yourTable
WHERE STR_TO_DATE(CreatedAt, '%a %b %d %H:%i:%s IST %Y') > NOW() - INTERVAL 1 HOUR;