Introduction
The main goal of this tip is to provide a set of functionality to SQL Server 2005 databases from which sunset and sunrise values will be determined from geopositional information provided as parameters and return a DateTime
value.
The main goal for this set of functions is to be used as an astronomic calculator for daylight related calculations that need to be done from DB directly.
Background
This is an implementation of the United States Naval Observatory's sunset/sunrise algorithm for SQL Server and probably (but not tested) compatible with most of the SQL based engines available in the market.
Using the Code
The usage is pretty straight forward. Two main stored procedures are included: DetermineSunrise
and DetermineSunset
. Each of them will expect date and geo location parameters.
DECLARE @OffSet int
SET @OffSet = dbo.udf_getUTCOffSet()
Exec DetermineSunrise
'05/11/2012',
-34.58,
-58.3,
@OffSet
Exec DetermineSunset
'05/11/2012',
-34.58,
-58.3,
@OffSet
OUTPUT
2012-05-11 07:37:19.200
2012-05-11 18:01:19.200
These are the related functions:
CREATE FUNCTION [dbo].[TimeFromDecimal]
(
@dTime decimal(28,4),
@dateTime datetime
)
RETURNS
datetime
AS
BEGIN
DECLARE @iHour int, @iMin int, @iSec int, @imSec int
set @iHour = @dTime
set @iMin = ((@dTime - @iHour)*60.)
set @iSec = (((@dTime-@iHour)*60-@iMin)*60)
set @imSec = ((((@dTime-@iHour)*60-@iMin)*60) - @iSec)* 1000
if @iHour > 0
begin
set @dateTime = dateadd(hh, @ihour, @dateTime)
end
else
begin
set @dateTime = dateadd(d, 1, @datetime)
set @dateTime = dateadd(hh, @ihour, @dateTime)
end
set @dateTime = dateadd(mi, @imin, @dateTime)
set @dateTime = dateadd(s, @isec, @dateTime)
set @dateTime = dateadd(ms, @imsec, @dateTime)
RETURN
@dateTime
END
CREATE FUNCTION [dbo].[udf_calcDayofYear]
(
@dDate datetime
)
RETURNS int
AS
BEGIN
DECLARE @N1 int, @N2 int, @N3 int, @N int
set @N1 = floor(275 * (select month(@dDate)) / 9)
set @N2 = floor(((select month(@dDate)) + 9) / 12)
set @N3 = (1 + floor(((select year(@dDate)) -
4 * floor((select year(@dDate)) / 4) + 2) / 3))
set @N = @N1 - (@N2 * @N3) + (select day(@dDate)) - 30
RETURN @N
END
CREATE FUNCTION [dbo].[udf_getUTCOffSet]()
RETURNS int
AS
BEGIN
RETURN DATEDIFF(hh, GETUTCDATE(), GETDATE())
END
Stored procedures described in implementation:
CREATE PROCEDURE [dbo].[DetermineSunrise]
@localDate datetime,
@latitude decimal(18,3),
@longitude decimal(18,3),
@GMT int
AS
DECLARE @ZENITH decimal(18,3)
SET @ZENITH = 90.83
DECLARE @DayOfYear int
SET @DayOfYear = dbo.udf_calcDayofYear(@localDate)
DECLARE @lngHour decimal(18,3)
DECLARE @t decimal(18,3)
SET @lngHour = @longitude / 15
SET @t = @DayOfYear + ((6 - @lngHour) / 24)
DECLARE @Mean decimal(18,3)
SET @Mean = (0.9856 * @t) - 3.289
DECLARE @SunLon decimal(18,3)
SET @SunLon = @Mean + (1.916 * sin(RADIANS(@Mean))) +
(0.020 * sin(2 * RADIANS(@Mean))) + 282.634 - 360
DECLARE @SunRightAsention decimal(18,3)
SET @SunRightAsention = DEGREES(atan(0.91764 * tan(RADIANS(@SunLon))))
DECLARE @Lquadrant decimal(18,3)
DECLARE @Rquadrant decimal(18,3)
SET @Lquadrant = (floor( @SunLon/90)) * 90
SET @Rquadrant = (floor(@SunRightAsention/90)) * 90
SET @SunRightAsention = @SunRightAsention + (@Lquadrant - @Rquadrant)
SET @SunRightAsention = @SunRightAsention / 15
DECLARE @sinDec decimal(18,3)
DECLARE @cosDec decimal(18,3)
SET @sinDec = 0.39782 * sin(RADIANS(@SunLon))
SET @cosDec = cos(asin(@sinDec))
DECLARE @cosH decimal(18,3)
SET @cosH = (cos(radians(@ZENITH)) - (@sinDec *
sin(radians(@latitude)))) / (@cosDec * cos(radians(@latitude)))
DECLARE @H decimal(18,3)
SET @H = 360 - degrees(ACOS(@cosH))
SET @H = @H / 15
DECLARE @MeanTime decimal(18,3)
SET @MeanTime = @H + @SunRightAsention - (0.06571 * @t) - 6.622
DECLARE @UT decimal(18,3)
SET @UT = @MeanTime - @lngHour
SELECT dbo.TimeFromDecimal(@UT + @GMT, @localDate)
CRATE PROCEDURE [dbo].[DetermineSunset]
@localDate datetime,
@latitude decimal(18,3),
@longitude decimal(18,3),
@GMT int
AS
DECLARE @ZENITH decimal(18,3)
SET @ZENITH = 90.83
DECLARE @DayOfYear int
SET @DayOfYear = dbo.udf_calcDayofYear(@localDate)
DECLARE @lngHour decimal(18,3)
DECLARE @t decimal(18,3)
SET @lngHour = @longitude / 15
SET @t = @DayOfYear + ((18 - @lngHour) / 24)
DECLARE @Mean decimal(18,3)
SET @Mean = (0.9856 * @t) - 3.289
DECLARE @SunLon decimal(18,3)
SET @SunLon = @Mean + (1.916 * sin(RADIANS(@Mean))) +
(0.020 * sin(2 * RADIANS(@Mean))) + 282.634 - 360
DECLARE @SunRightAsention decimal(18,3)
SET @SunRightAsention = DEGREES(atan(0.91764 * tan(RADIANS(@SunLon))))
DECLARE @Lquadrant decimal(18,3)
DECLARE @Rquadrant decimal(18,3)
SET @Lquadrant = (floor( @SunLon/90)) * 90
SET @Rquadrant = (floor(@SunRightAsention/90)) * 90
SET @SunRightAsention = @SunRightAsention + (@Lquadrant - @Rquadrant)
SET @SunRightAsention = @SunRightAsention / 15
DECLARE @sinDec decimal(18,3)
DECLARE @cosDec decimal(18,3)
SET @sinDec = 0.39782 * sin(RADIANS(@SunLon))
SET @cosDec = cos(asin(@sinDec))
DECLARE @cosH decimal(18,3)
SET @cosH = (cos(radians(@ZENITH)) - (@sinDec *
sin(radians(@latitude)))) / (@cosDec * cos(radians(@latitude)))
DECLARE @H decimal(18,3)
SET @H = degrees(ACOS(@cosH))
SET @H = @H / 15
DECLARE @MeanTime decimal(18,3)
SET @MeanTime = @H + @SunRightAsention - (0.06571 * @t) - 6.622
DECLARE @UT decimal(18,3)
SET @UT = @MeanTime - @lngHour
SELECT dbo.TimeFromDecimal(@UT + @GMT, @localDate)
Hope you find this information useful. Happy coding!