Sql Query to Select from 1 Hour Ago

Query where another row's datetime is over 1 hour ago

You can re-write your query using EXISTS as follows:

SELECT t.* 
  FROM tickets t join ticket_updates tu on t.ticketnumber = tu.ticketnumber 
 WHERE t.status = 'Pending Completion' 
   AND tu.type = 'update' 
   AND tu.datetime < NOW() - INTERVAL 1 HOUR
   AND NOT EXISTS 
       (SELECT 1 FROM ticket_updates tuu
         WHERE tu.ticketnumber  = tuu.ticketnumber 
           AND tuu.type = 'update' 
           AND tuu.datetime < NOW() - INTERVAL 1 HOUR
           AND tuu.datetime > tu.datetime
       )

If you are running on mysql 8.0+ then you can use analytical function as follows:

SELECT * FROM
(SELECT t.*, row_number() over (partition by tu.ticketnumber order by tu.datetime) as rn 
  FROM tickets t join ticket_updates tu on t.ticketnumber = tu.ticketnumber 
 WHERE t.status = 'Pending Completion' 
   AND tu.type = 'update' 
   AND tu.datetime < NOW() - INTERVAL 1 HOUR) t
 WHERE RN = 1

MySQL to return records with a date/time of now minus 1 hour?

You are very close. The most readable way, in my opinion, to write WHERE conditions involving date / times is:

 WHERE date_of_event >= NOW() - INTERVAL 1 HOUR

Why? >= for date/time values means on or after. You need to be able to look at your query code and reason about it. That formulation clearly matches your specification:

a date/time equal to or more than now - 1hour?

But: you say you want to trigger an event one hour after the time. That probably means you want to choose

a date/time one hour or more ago, that is a date/time equal to or less than now - 1 hour.

That's what you have already. But I would rewrite it

WHERE date_of_event <= NOW() - INTERVAL 1 HOUR

<= means on or before.

Pro tip Avoid backticks unless your columns or tables have the same names as reserved words like SELECT or GROUP. Avoid naming your columns or tables with reserved words. Backticks look so much like single-quotes that it's easy to get confused.

Pro tip date/time expressions with = in them almost never come up true, because the equality must be exact, down to the second or millisecond. So avoid conditions like date_of_event = NOW() - INTERVAL 1 HOUR.

MySQL select records 1 hour ago or fresher on datetime column

Something like this? I assume an DATETIME timestamp is an DATETIME field.

SELECT * FROM table WHERE datetimefield >= DATE_SUB(NOW(), INTERVAL 1 HOUR)

For more information check MySQL's date/time functions.

SQL Query to show hour before current time

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)

SQL Query by created 3 hours ago

Use NOW() and INTERVAL in your WHERE clause

SELECT * FROM items WHERE date_created <= NOW() - INTERVAL 180 minute AND date_created >= NOW() - INTERVAL 210 minute

Get records from last hour

try this !!

SELECT Field1, OrderFor, Writeback, Actshipdate, Orderstatus, receivedate, receivetime
    FROM orderinfo, shippinginfo
    WHERE orderinfo.orderid = shippinginfo.orderid
    AND shippinginfo.custid = '37782'
    AND receivedate =  DATE(NOW())
     AND receivetime > DATEADD(HOUR, -1, GETDATE())

SELECT past hour data

Well, now() returns a timestamp with time zone, not a time value. And you can't compare a time value with a timestamp with time zone value.

You need to use localtime:

SELECT id, name, value, "date", "time"
FROM boxbox 
WHERE "time" >= localtime - INTERVAL '1 hour'
ORDER BY "time" desc

However the above won't work properly if the time crossed midnight. If you also need to take care of that, you need to combine your date and time columns to a single timestamp column:

SELECT id, name, value, "date", "time"
FROM boxbox 
WHERE "date" + "time" >= now () - INTERVAL '1 hour'
ORDER BY "time" desc

Get most recent older value from one hour ago in mysql

The Following query will get the current hour and subtract one from it and then compare it to the hour of t_stamp.

SELECT locid, value
FROM  table2
WHERE DATE(t_stamp) = DATE(CURRENT_DATE)
AND HOUR(t_stamp) = HOUR(CURRENT_TIME) -1
AND locid = '2815'
ORDER BY t_stamp desc
LIMIT 1

Or the following query might be what you are looking for:

SELECT locid, value
FROM  table2
WHERE t_stamp <= DATE_SUB(NOW(), INTERVAL 1 HOUR)
AND locid = '2815'
ORDER BY t_stamp desc
LIMIT 1

---

Related Topics