Member Avatar for justapimp

I need some help creating a query that will pull a series of record that falls with a date range with specific interval.

for example: I would like pass in a date like getdate() - 10 for start date and getdate() for the end date. However, the dates in between (which is the timestamp of the record) the two dates should be between (x second, minutes or hours apart).:(

  • 3 Contributors
  • 3 Replies
  • 532 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by darkagn

Dani AI

Generated

wanted records inside a broad start/end window but only those that fall within +/- a small tolerance of regularly spaced anchor times (for example every N seconds/minutes). 's BETWEEN and 's DATEADD point in the right direction; the full solution is to generate the anchor timestamps (every N seconds) and then match rows that lie within the tolerance of each anchor.

A reliable set-based pattern is: produce the series of anchors (use a persistent numbers/tally table for large ranges or a small recursive CTE for short ranges), then JOIN the data using a sargable range predicate around each anchor. This keeps the DateTime column free of functions (so an index can be used) and makes the logic explicit.

Example (anchors -> join to matches):

WITH anchors AS (
  -- generate anchor_ts = @start + n * @intervalSec
  -- use a numbers table or a recursive CTE here
)
SELECT anc.anchor_ts, a.*
FROM anchors anc
JOIN address a
  ON a.DateTime BETWEEN DATEADD(SECOND, -@tolSec, anc.anchor_ts)
                    AND DATEADD(SECOND,  @tolSec, anc.anchor_ts);

When the intent is "one best match per anchor" use APPLY/TOP(1) to pick the nearest timestamp within tolerance (ORDER BY ABS(DATEDIFF(SECOND, anc.anchor_ts, a.DateTime))). Be aware that if interval < 2*tolerance, anchors overlap and the same row can match multiple anchors — resolve by choosing the nearest, changing tolerance, or deduping afterwards.

Operational notes: prefer a persistent numbers table for large spans, ensure a nonclustered index on the DateTime column, avoid wrapping the DateTime column in functions in WHERE clauses, and store timestamps in UTC (use datetime2 for higher precision where available). These adjustments improve correctness and performance for production workloads.

Recommended Answers

All 3 Replies

Member Avatar for freshfitz 
select * from address
WHERE DateTime
BETWEEN '2009-01-31 09:00:00' AND '2009-01-31 09:34:34'
Member Avatar for justapimp

What I like to get done is a bit more complex than that; but thank you for the partial solution. what you have will only give me all the dates that are between the start and the end date.

What I really need help with is to be able to look for dates that are between the start and the stop date in addition to return all the occurrences that are within +/- say 15sec. of the broad interval's start time.

Member Avatar for darkagn

The DATEADD function will do what you need. Its syntax is DATEADD(datepart,number,date) where the datepart parameter is the interval that you are adding (eg second which can be abbreviated to ss), the number parameter is the amount you are adding (eg 15 or -15) and the date parameter is the date that you are adding to (eg '2009-03-17 12:15:45'). So your query, using freshfitz's example, would be:

SELECT * FROM address
WHERE DateTime
BETWEEN DATEADD(ss,-15,'2009-01-31 09:00:00') AND DATEADD(ss,15,'2009-01-31 09:34:34')

HTH,
d :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.