Most Active Time for User with UTC Time Shift

0

Please login or register to vote for this query.

(click on this box to dismiss)

TeX Meta

Q&A about the site for users of TeX, LaTeX, ConTeXt, and related typesetting systems

DECLARE @UserId int = ##UserId##

; WITH TimeZoneShifts AS
(
	SELECT 0 AS Shift UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
	SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
	SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
	SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
	SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
	SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
)
, Times AS 
(
  SELECT AvgTime = AVG(AvgTime)
	, StdevTime = AVG(StdevTime)
	, C = SUM(C)
	, Shift
  FROM
  (
	  SELECT AvgTime = AVG(((DATEPART(HH, c.CreationDate)+t.Shift)%24*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) 
		, StdevTime = STDEV(((DATEPART(HH, c.CreationDate)+t.Shift)%24*100) + (DATEPART(mi, c.CreationDate) * 100 / 60))
		, C = COUNT(*) 
		, t.Shift
	  FROM Users u
	  CROSS JOIN TimeZoneShifts t
	  LEFT JOIN Comments c
		ON u.Id = c.UserId
	  WHERE u.Id = @UserId
	  GROUP BY t.Shift
	  
		UNION ALL
	    
	  SELECT AvgTime = AVG(((DATEPART(HH, c.CreationDate)+t.Shift)%24*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) 
		, StdevTime = STDEV(((DATEPART(HH, c.CreationDate)+t.Shift)%24*100) + (DATEPART(mi, c.CreationDate) * 100 / 60)) 
		, C = COUNT(*) 
		, t.Shift
	  FROM Users u
	  CROSS JOIN TimeZoneShifts t
	  LEFT JOIN Posts c
		ON u.Id = c.OwnerUserId
	  WHERE u.Id = @UserId
	  GROUP BY t.Shift
  ) a
  GROUP BY Shift
)
SELECT StartTime = CAST(CAST((CAST(StartHour+24-Shift AS INT)%24) AS VARCHAR(2)) + ':' + CAST(StartMinute AS VARCHAR(2)) AS TIME) 
  , EndTime = CAST(CAST((CAST(EndHour+24-Shift AS INT)%24) AS VARCHAR(2)) + ':' + CAST(EndMinute AS VARCHAR(2)) AS TIME) 
  , Shift
  , StdevTime
FROM
(
  SELECT StartHour = FLOOR(StartTime/100) 
    , StartMinute = FLOOR((CAST(FLOOR(StartTime) AS INT)%100) * 60 / 100) 
    , EndHour = FLOOR(EndTime/100) 
    , EndMinute = FLOOR((CAST(FLOOR(EndTime) AS INT)%100) * 60 / 100) 
    , Shift
    , StdevTime
  FROM
  (
    SELECT StartTime = AVG(AvgTime) - AVG(StdevTime) 
      , EndTime = AVG(AvgTime) + AVG(StdevTime) 
      , StdevTime
      , Shift
    FROM Times
    GROUP BY Shift, StdevTime
  ) a
) a
ORDER BY StdevTime ASC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results