CODEWITHSUNDEEP
sqlsql-server-2008
user3538102
user3538102

Reputation: 171

Return hourly and half hourly data

I have a database that has numerous tables and I have joined two of the tables together and it is working fine but I now want to return the data that falls on half hour intervals! I have read numerous articles on this and I cant seem to get it to work. Below shows an example of my database

enter image description here

Below is the SQL that I have written to join the two tables

SELECT     
 Table2.SourceID, 
 Table2.Value, 
 Table1.Name, 
 Table2.TimestampUTC
 FROM         Table2 INNER JOIN
                  Source ON Table1.SourceID = Table1.ID 
 Where Table2.Value is not NULL
 ORDER BY Table2.TimestampUTC DESC

I have tried numerous examples so any guidance would be appreciated!

Upvotes: 0

Views: 853

Answers (3)

Jeffrey Wieder
Jeffrey Wieder

Reputation: 2376

This is very easy to do with use of the DatePart Function

Add to your where clause:

AND DATEPART(minute, TIMESTAMP) in (0, 30)

To filter for a particular date add:

//Converting to varchar with sytle 101 strips off the time values, then we convert it back to a datateime for comparison
AND CONVERT(datetime, CONVERT(VARCHAR, TIMESTAMP, 101)) = '1/1/2015'

To filter for Today add:

//Converting to varchar with sytle 101 strips off the time values, then we convert it back to a datateime for comparison
AND CONVERT(datetime, CONVERT(VARCHAR, TIMESTAMP, 101)) = CONVERT(datetime, CONVERT(VARCHAR, GETDATE(), 101))

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

You can use the DATEPART function to extract the minutes from a datetime value:

SELECT     
 Table2.SourceID, 
 Table2.Value, 
 Table1.Name, 
 Table2.TimestampUTC
 FROM         Table2 INNER JOIN
                  Source ON Table1.SourceID = Table1.ID 
 Where Table2.Value is not NULL
 and DATEPART(minute,Table2.TimestampUTC) in (0,30)
 ORDER BY Table2.TimestampUTC DESC

(Usually, you'd be advised not to apply functions to columns so that indexes might be useful but, given the nature of this query, it's unlikely that a way can be found to write it that would be able to benefit from indexes anyway)

If you only want rows from today, then an additional filter can be applied:

SELECT     
 Table2.SourceID, 
 Table2.Value, 
 Table1.Name, 
 Table2.TimestampUTC
 FROM         Table2 INNER JOIN
                  Source ON Table1.SourceID = Table1.ID 
 Where Table2.Value is not NULL
 and DATEPART(minute,Table2.TimestampUTC) in (0,30)
 and Table2.TimestampUTC >= DATEADD(day,DATEDIFF(day,0,CURRENT_TIMESTAMP),0)
 and Table2.TimestampUTC < DATEADD(day,DATEDIFF(day,0,CURRENT_TIMESTAMP),1)
 ORDER BY Table2.TimestampUTC DESC

Where DATEADD(day,DATEDIFF(day,0,CURRENT_TIMESTAMP),0) computes "today at the starting midnight" and DATEADD(day,DATEDIFF(day,0,CURRENT_TIMESTAMP),1) computes "tomorrow at the starting midnight" and these comparisons to TimestampUTC might be able to benefit from an index on that column now.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269513

If you want the data on half hour intervals, then you can use the minute component of the datetime field:

SELECT t2.SourceID, t2.Value, s.Name, t2.TimestampUTC
FROM Table2 t2 INNER JOIN
     Source s
     ON t2.SourceID = t2.ID 
WHERE t2.Value is not NULL AND
      datepart(minute, t2.TimestampUTC) in (0, 30)
ORDER BY t2.TimestampUTC DESC;

I also fixed your query so you don't have undefined table aliases, such as table1.

Upvotes: 1

Related Questions