Return data only if time is equal by a minute

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return data only if time is equal by a minute

Author

Topic

Alain_TV
Starting Member

12 Posts

Posted - 2015-04-09 : 04:18:15

Hi All,

Just wondering what is the best time to ensure that we only return data when the datetime field is the same when compared between two datetimes within a minute difference.

As in the following should return the data:

'2015-04-09 09:00:20' compared to '2015-04-09 09:00:50'

And the following should not return the data:

'2015-04-09 09:01:20' compared to '2015-04-09 09:00:50'

The problem, is that I'm merging data from three different result sets, which they all have data for every minute, however, the timestamp can be different by seconds or milliseconds.

So, I'm only interested to return the data when the two fields that I'm comparing are equal within a minute. I need to ignore seconds and milliseconds.

Any help would be much appreciated.

Regards,
Alain

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-04-09 : 05:01:47

Should 09:00:59 and 09:10:02 (apart with 3 seconds) count as true?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Alain_TV
Starting Member

12 Posts

Posted - 2015-04-09 : 06:29:12

Hi SwePeso,

The intent is to display three values from different rows, whenever the are equal when comparing the two timestamps.
Having said that, you do have a point. We need only one entry per minute across the two subqueries. I guess, that we will need to return the MAX from both datetimes and then compare them to make sure that they are similar in time, within a range of a minute is acceptable.

Say we have they following data:


ID       DateTimeStamp          Value_1    Value_2  
1     '2015-04-09 09:01:10'       23.34      45
2     '2015-04-09 09:01:12'       23.46      46
3     '2015-04-09 09:01:23'       22.59      46
4     '2015-04-09 09:02:34'       22.23      47
5     '2015-04-09 09:02:45'       23.28      45

From the above data rows, I would expect a return of any of the three rows from ID 1 to 3 (aka ignoring the seconds and milliseconds parts), but only one of them per minute.

That's it make more sense?

Regards,
Alain

Kristen
Test

22859 Posts

Posted - 2015-04-09 : 11:13:03

You can "round" a date/time to a whole minute with:


DATEADD(Minute, DATEDIFF(Minute, 0, DateTimeStamp), 0)

and you could then, perhaps?, compare/merge matching values

Kristen
Test

22859 Posts

Posted - 2015-04-09 : 11:14:23

P.S. If *I*

was merging data generated every minute I would not match 01:02:59 with 01:03:01 - if there is a value every minute then I would merge 01:02:59 with 01:02:01 instead.

I suppose the problem that arises is if you don't have a value for 01:02:01 - in fact you have no value for 01:02 but instead you have values for 01:03:01 and 01:03:59

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources