Getting time to 15 minute intervals

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

Getting time to 15 minute intervals

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-15 : 03:55:17

Hi all

I've got data in datetime format like this:-
2011-11-27 14:41:54.000
2011-11-30 18:32:14.000
2011-12-02 13:02:35.000
2011-11-27 12:48:48.000

and I need to get the 15 minute interval it falls in. I'll also need to keep the date as part of the new field.

The 15 minute intervals start at 00:00:00, 00:15:00, 00:30:00 and 00:45:00.

I've tried doing this in Excel but I can't seem to convert it to a SQl formula.

Any help greatly appreciated.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-12-15 : 05:36:52

Here's one way


DECLARE @dtTable TABLE (
	[dt] DATETIME
	)
INSERT @dtTable
VALUES ('2011-11-27 14:41:54.000'),
	   ('2011-11-30 18:32:14.000'),
	   ('2011-12-02 13:02:35.000'),
	   ('2011-11-27 12:48:48.000')

SELECT [dt] FROM @dtTable


SELECT
	[dt]
	, DATEADD(MINUTE, [dtMinuteSlot], [dtStripped]) AS [RoundedDate]
FROM
	(
	SELECT
		[dt]
		, DATEADD(HOUR, DATEDIFF(HOUR, '19700101', [dt]), '19700101') AS [dtStripped]
		, (DATEPART(MINUTE, [dt]) / 15) * 15 AS [dtMinuteSlot]
	FROM
		@dtTable
	)
	AS slotDt

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 05:42:41

see

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2011-12-15 : 06:30:09

I've got about 5000 records so far (and it will grow at the rate of about 750 a day) and I need to add the 15 minute interval as a separate field when I take the data from a holding table into my main table.
I was hoping there was a formula or similar that I could use.
I've managed to get one in Excel, but I can't translate it to SQL.

::edit::
On seconds thoughts, it's perfect.
I've just tried it on a known size table (around 12 million records) and it's given me the results in less than 2 minutes.

Can someone explain how it works please?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-12-15 : 06:50:23

you can derive that from what I posted

here:


CREATE FUNCTION fnToFifteenMinInterval (@dt DATETIME)
RETURNS DATETIME
AS BEGIN
	DECLARE @rdt DATETIME
	SELECT @rdt = 
		DATEADD(
			MINUTE
			, ((DATEPART(MINUTE, @dt) / 15) * 15)
			, DATEADD(HOUR, DATEDIFF(HOUR, '19700101', @dt), '19700101')
			)
	RETURN @rdt
END
GO

SELECT dbo.fnToFifteenMinInterval ('20111225 12:03:12.233' )

There are probably better ways.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources