Sunset and Sunrise Calculation Based on Latitude, Longitude and UTC Offset for SQL Server 2005

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()

-- Buenos Aires, Argentina, 34, 35 S, 58, 22 W, 11th May 2012
--
-- Parameters:
-- -------------
-- Today's date
-- latitude
-- longitude
-- UTC offset

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
-- Add the T-SQL statements to compute the return value here
DECLARE @iHour int, @iMin int, @iSec int, @imSec int
set @iHour = @dTime 
--concatenate hours
set @iMin = ((@dTime - @iHour)*60.) 
--subtract hours and convert to mins
--select mins and secs in dec form subtract mins and convert remainder to seconds:
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 --will subtract into the day before
    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 the result of the function
 
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

/*
	  ZENITH
	  offical      = 90 degrees 50'
	  civil        = 96 degrees
	  nautical     = 102 degrees
	  astronomical = 108 degrees
*/
--SUNSET
	--Calculate the day of the year
	DECLARE @DayOfYear int
	SET @DayOfYear = dbo.udf_calcDayofYear(@localDate)

	-- convert the longitude to hour value and calculate an approximate time

	DECLARE @lngHour decimal(18,3)
	DECLARE @t decimal(18,3)

	SET @lngHour = @longitude / 15
	SET @t = @DayOfYear + ((6 - @lngHour) / 24)

	-- calculate the Sun's mean anomaly

	DECLARE @Mean decimal(18,3)	
	SET @Mean = (0.9856 * @t) - 3.289

	-- calculate the Sun's true longitude

	DECLARE @SunLon decimal(18,3)

	SET @SunLon = @Mean + (1.916 * sin(RADIANS(@Mean))) + 
	(0.020 * sin(2 * RADIANS(@Mean))) + 282.634 - 360


	---- calculate the Sun's right ascension

	DECLARE @SunRightAsention decimal(18,3)
	SET @SunRightAsention = DEGREES(atan(0.91764 * tan(RADIANS(@SunLon))))

	---- right ascension value needs to be in the same quadrant as L

	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)

	----right ascension value needs to be converted into hours

	SET @SunRightAsention = @SunRightAsention / 15

	---- calculate the Sun's declination

	DECLARE @sinDec decimal(18,3)
	DECLARE @cosDec decimal(18,3)

	SET @sinDec = 0.39782 * sin(RADIANS(@SunLon))
	SET @cosDec = cos(asin(@sinDec))

	---- calculate the Sun's local hour angle
	DECLARE @cosH decimal(18,3)	
	SET @cosH = (cos(radians(@ZENITH)) - (@sinDec * 
	sin(radians(@latitude)))) / (@cosDec * cos(radians(@latitude)))


	----if (cosH >  1) 
	----  the sun never rises on this location (on the specified date)
	----if (cosH < -1)
	----  the sun never sets on this location (on the specified date)

	DECLARE @H decimal(18,3)
	SET @H = 360 - degrees(ACOS(@cosH))
	SET @H = @H / 15

	---- calculate local mean time of rising/setting
	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

/*
	  ZENITH
	  offical      = 90 degrees 50'
	  civil        = 96 degrees
	  nautical     = 102 degrees
	  astronomical = 108 degrees
*/
--SUNSET
	--Calculate the day of the year
	DECLARE @DayOfYear int
	SET @DayOfYear = dbo.udf_calcDayofYear(@localDate)

	-- convert the longitude to hour value and calculate an approximate time

	DECLARE @lngHour decimal(18,3)
	DECLARE @t decimal(18,3)

	SET @lngHour = @longitude / 15
	SET @t = @DayOfYear + ((18 - @lngHour) / 24)

	-- calculate the Sun's mean anomaly

	DECLARE @Mean decimal(18,3)	
	SET @Mean = (0.9856 * @t) - 3.289

	-- calculate the Sun's true longitude

	DECLARE @SunLon decimal(18,3)

	SET @SunLon = @Mean + (1.916 * sin(RADIANS(@Mean))) + 
	(0.020 * sin(2 * RADIANS(@Mean))) + 282.634 - 360


	---- calculate the Sun's right ascension

	DECLARE @SunRightAsention decimal(18,3)
	SET @SunRightAsention = DEGREES(atan(0.91764 * tan(RADIANS(@SunLon))))

	---- right ascension value needs to be in the same quadrant as L

	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)

	----right ascension value needs to be converted into hours

	SET @SunRightAsention = @SunRightAsention / 15

	---- calculate the Sun's declination

	DECLARE @sinDec decimal(18,3)
	DECLARE @cosDec decimal(18,3)

	SET @sinDec = 0.39782 * sin(RADIANS(@SunLon))
	SET @cosDec = cos(asin(@sinDec))

	---- calculate the Sun's local hour angle
	DECLARE @cosH decimal(18,3)	
	SET @cosH = (cos(radians(@ZENITH)) - (@sinDec * 
	sin(radians(@latitude)))) / (@cosDec * cos(radians(@latitude)))


	----if (cosH >  1) 
	----  the sun never rises on this location (on the specified date)
	----if (cosH < -1)
	----  the sun never sets on this location (on the specified date)

	DECLARE @H decimal(18,3)
	SET @H = degrees(ACOS(@cosH))
	SET @H = @H / 15

	---- calculate local mean time of rising/setting
	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!