Get Records 10 Min Before System Datetime in SQL

Get records 10 min before system datetime in SQL

select Id, TimeStamp
from ISAlive
WHERE RecordUpdatedDate = dateadd(minute,-10,getdate())

might be a starting point. Of course, it probably won't match exactly...

...if you want to get the most recent record that fits that criteria, however, try

SELECT TOP 1 ID, TimeStamp
FROM ISAlive
WHERE RecordUpdatedDate <= dateadd(minute, -10, getdate())
ORDER BY RecordUpdatedDate DESC

Get all transactions prior to 3 minutes ago with SQL

Yes, that is the correct way to use dateadd() for 10 minutes ago if your transactionDate is based on server time and is a datetime datatype.

select *
from [transaction]
where transactionType = 'CreditCard' 
  and transactionDate < dateadd(minute, -3, getdate())

If you wanted to truncate to the minute, you would use:

select *
from [transaction]
where transactionType = 'CreditCard' 
  and transactionDate < dateadd(minute, datediff(minute, 0, getdate() )-3, 0)

To avoid any implicit conversions between datetime and datetime2(7):

If transactionDate is local time and a datetime datatype, then you would use getdate().

If transactionDate is local time and a datetime2 datatype, then you would use sysdatetime().

If transactionDate is UTC time (Coordinated Universal Time) and a datetime datatype, then you would use getutcdate().

If transactionDate is UTC time (Coordinated Universal Time) and a datetime2 datatype, then you would use sysutcdatetime().

The differences between datatype and function results are explained here: sysdatetime().

How to select all records that are 10 minutes within current timestamp in MySQL?

The most efficient way would be to compare your timestamp (and only timestamp, without using any functions on it) to an expression that can be calculated as constant for every row, this way mysql can use index defined on your timestamp column.

SELECT * FROM myTable
WHERE last_seen >= NOW() - INTERVAL 10 MINUTE

You can always try EXPLAIN SELECT ... to see if an index can be used to find rows satisfying your WHERE condition without the need to check every row in the table.

Pull Data From Last 10 Minutes

Just FYI, the code that you're using, while it works, isn't up to the ANSI standard which is now almost 20 years old. This separates joins from the WHERE clause:

SELECT t.ps_ticket_id, l.loc_desc, n.descrip, a.itemno, p.print_last_date
  FROM fgmulti f
 INNER JOIN locations l
    ON f.loc_id = l.id
 INNER JOIN v_ps_tickets t
    ON f.arinvt_id = t.arinvt_id
 INNER JOIN non_conform n
    ON n.id = f.non_conform_id 
 INNER JOIN arinvt a
    ON f.arinvt_id = a.id
 INNER JOIN ps_ticket p
    ON p.id = t.ps_ticket_id
 WHERE p.print_last_date >= SYSDATE - 1/144;

(Since there are 1,440 minutes in a day, 1/144 = 10 minutes. You can also use intervals, e.g., - INTERVAL '10' MINUTE, but I don't see a good reason to conform to the ANSI standard here when using a non-standard function -- SYSDATE -- anyway.)

Hope this helps.

WHERE datetime older than some time (eg. 15 minutes)

older would be WHERE creation_date < DATE_SUB(NOW(),INTERVAL 15 MINUTE)

---

Related Topics