TSQL Query - return all seconds between two dates

Question

I need a TSQL query which returns all seconds since a given begin date. I am going to use this to left outer join against another table to see how many bytes were downloaded each second. I am using Sql Server 2008.

Answer 1

You will likely need an Auxiliary numbers table for this. Do you need all seconds represented or can you just round to the nearest second and group by that?

Also how many seconds are we talking about here and what format do you currently have them stored in. Are they already rounded?

If not then maybe to avoid the overhead of rounding them or doing a BETWEEN type query every time (as well as the repeated DATEADDs) maybe you could use Marc's DATEDIFF answer at insert/update time to store the seconds from some base date then just join onto the numbers table using the calculated numeric column.

Code to create Numbers table from here http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html Just to add to Brad's answer.

CREATE TABLE dbo.Numbers 
( 
    Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED 
) 

WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1000000
BEGIN 
    INSERT dbo.Numbers DEFAULT VALUES 
END

Answer 2

I'm shooting from the hip here, but here's a start:

DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME

SET @STARTDATE = '05/01/2010'
SET @ENDDATE = GETDATE()

-- Find the seconds between these two dates
SELECT DATEADD(SECOND, Number, @StartDate) AS N
FROM Numbers
WHERE Number < DATEDIFF(SECOND, @STARTDATE, @ENDDATE)

This assumes a table called Numbers, with a column named Number containing values from 1 up. The be able to get results for an entire month, you're going to need to have values up to around 2.5 million. I'd keep the query down to perhaps a day, meaning the Numbers table can get away with values less than 100,000.

Here's a great article on numbers tables: http://www.sqlservercentral.com/articles/Advanced+Querying/2547/

Registration is required, but it's free. If you do any serious SQL Server programming this site is quite useful.