Get last 6 hrs

[Gil_]

I have a table with a datetime field i need to get to pull whst the entry was 6 hrs ago

Id. Datetime. Balance
. 21sep2018:12:22:30. 100
B2. 21sep2018:10:22:30. 189
A6. 21sep2018:12:09:30. 65
Whst i want is to pull report showing what they did 6 hrs ago

Id. Datetime. Balance
. 21sep2018:06:22:30. 233
B2. 21sep2018:04:22:30. 35
A6. 21sep2018:03:09:30. 64

[ballardw]

---

@Gil_ wrote:
I have a table with a datetime field i need to get to pull whst the entry was 6 hrs ago

Id. Datetime. Balance
. 21sep2018:12:22:30. 100
B2. 21sep2018:10:22:30. 189
A6. 21sep2018:12:09:30. 65
Whst i want is to pull report showing what they did 6 hrs ago

Id. Datetime. Balance
. 21sep2018:06:22:30. 233
B2. 21sep2018:04:22:30. 35
A6. 21sep2018:03:09:30. 64

---

Exactly to the second (or fraction thereof) 6 hours ago? Or "closest to 6 hours" (might be 5 hours and 58 minutes). Or the closest at least six hours ago (six hours and three minutes?

[Gil_]

Yes as close as 6 hrs

[SASKiwi]

You need to be more precise explaining your requirements.

For example, for each ID value get the row with the datetime that is closest to being no more than 6 hours prior to the time the query runs. Does this agree with what you want or not?

[mkeintz]

What do you want to do with the first 5-6 hours of data records?   How close to 6 hours prior to the record-in-hand  must the target observation be?  What is the sort order of incoming data, and how big is it?

[Shmuel · Accepted solution]

You can use function INTCK to check the time interval between two dates / datetimes / times:

SYNTAX:

INTCK(interval<multiple><.shift-index>, from, to)

You should define interval as "hour". Then check the interval is it less or equal to 6.