CODEWITHSUNDEEP
phpmysql
Trevor Ackermann
Trevor Ackermann

Reputation: 176

SQL Query to show hour before current time

I have a MySQL DB with date time format as follow

0000-00-00 00:00:00

I want to run a SQL query to show me any information older than one hour but not older than 4 hours from the current time

SELECT * FROM gs_objects WHERE dt_server < NOW()

This currently shows me all information older than current time, how can I change that to give me information older than one hour but not older than 4 hours

Upvotes: 1

Views: 3180

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

You can use date_sub to find the datetime range:

select *
from gs_objects
where dt_server between date_sub(now(), interval 4 hour)
                and date_sub(now(), interval 1 hour)

if you don't want the rows with datetime exactly 4 hours before, you can use:

select *
from gs_objects
where dt_server > date_sub(now(), interval 4 hour)
and   dt_server <= date_sub(now(), interval 1 hour)

Upvotes: 2

Scuzzy
Scuzzy

Reputation: 12332

Untested, but this should get what you're chasing.

Using DATE_SUB to find the time 1 hour and 4 hours ago with the comparison.

where dt_server < DATE_SUB( NOW(), INTERVAL 1 HOUR )
  and dt_server > DATE_SUB( NOW(), INTERVAL 4 HOUR )

Upvotes: 0

Related Questions