Beginning of the Minute (T-SQL) and One Usage Example


We can use dateadd and datefiff functions to retrieve the beginning of current minute with this query:


 SELECT  dateadd(mi, datediff(mi, 0, CURRENT_TIMESTAMP), 0) beginningOfTheMinute

We can use this method to retrieve last five seconds in any given minutes from a datetime column.
Here is a query to solve the problem I saw in this thread:
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/1ee3bc49-08ff-4191-8a57-2813521ae122

There are two solutions given in the thread but with this method we can come up with a query like this:

 SELECT pulse_timestamp,pulse_value
 --dateadd(mi, datediff(mi, 0, pulse_timestamp)+1, 0),
 --dateadd(s,-5, dateadd(mi, datediff(mi, 0, pulse_timestamp)+1, 0)),
   FROM Pulse_Readings
  WHERE  pulse_timestamp=dateadd(s,-5, dateadd(mi, datediff(mi, 0, pulse_timestamp)+1, 0))

You can test the query with Mr. Celko’s sample table:


CREATE TABLE Pulse_Readings
 (pulse_timestamp DATETIME2(0) NOT NULL PRIMARY KEY,
  pulse_value INTEGER NOT NULL
  CHECK (pulse_value > 0));
 

INSERT INTO Pulse_Readings
 VALUES  
 ('2012-11-29 07:58:48', 930), ('2012-11-29 07:58:53', 932), ('2012-11-29 07:58:58', 933),--   
 ('2012-11-29 07:59:03', 935), ('2012-11-29 07:59:08', 935), ('2012-11-29 07:59:13', 937), 
 ('2012-11-29 07:59:18', 938), ('2012-11-29 07:59:23', 940), ('2012-11-29 07:59:27', 941), 
 ('2012-11-29 07:59:33', 943), ('2012-11-29 07:59:38', 944), ('2012-11-29 07:59:43', 945), 
 ('2012-11-29 07:59:48', 946), ('2012-11-29 07:59:53', 948), ('2012-11-29 07:59:58', 949), --   
 ('2012-11-29 08:00:03', 1), ('2012-11-29 08:00:08', 2), ('2012-11-29 08:00:13', 3), 
 ('2012-11-29 08:00:18', 4), ('2012-11-29 08:00:23', 6), ('2012-11-29 08:00:28', 07), 
 ('2012-11-29 08:00:33', 9), ('2012-11-29 08:00:38', 9), ('2012-11-29 08:00:43', 11), 
 ('2012-11-29 08:00:48', 12), ('2012-11-29 08:00:53', 14), ('2012-11-29 08:00:58', 15),-- 
 ('2012-11-29 08:01:03', 17), ('2012-11-29 08:01:08', 18), ('2012-11-29 08:01:13', 19), 
 ('2012-11-29 08:01:18', 20), ('2012-11-29 08:01:23', 22), ('2012-11-29 08:01:28', 23), 
 ('2012-11-29 08:01:33', 25), ('2012-11-29 08:01:38', 26), ('2012-11-29 08:01:43', 27), 
 ('2012-11-29 08:01:48', 28), ('2012-11-29 08:01:53', 30), ('2012-11-29 08:01:58', 31), -- 
 ('2012-11-29 08:02:03', 33), ('2012-11-29 08:02:08', 34), ('2012-11-29 08:02:13', 35);

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s