Display Sunrise and Sunset Time Using VB.NET and MySQL Database

In this tutorial, we demonstrate you on how to display visitor’s sunrise and sunset time based on their IP address using VB.NET programming languages and IP2Location MySQL database. In this tutorial, we use the IP2Location LITE database to lookup the country of origin from the visitor’s IP address. Free databases are available for download at IP2Location LITE database.

Step 1: Download IP2Location LITE database, unzip the file follow the instruction in order to create the database table. Please refer to DB11 LITE for further information.

Step 2: Download the demo project at Display sunrise and sunset time using VB.NET and include it into your VB.NET project.

Below are the steps to set up the database for both IPv4 and IPv6 data and the sample codes

Calculation for sunrise and sunset time

         Protected Function calculate(input_date As DateTime, set_rise As Integer, offset As Double)
         'Convert longitude into hour value
         Dim long_hour As Double = longitude / 15
         Dim t As Double

        'sunset = 0, sunrise = 1
        'calculate approximate time
        If (set_rise = 1) Then
            t = input_date.DayOfYear + ((6 - long_hour) / 24)
        ElseIf (set_rise = 0) Then
            t = input_date.DayOfYear + ((18 - long_hour) / 24)
        End If

        'Calculate Sun's mean anomaly time
        Dim mean As Double = (0.9856 * t) - 3.289

        'Calculate Sun's true longitude
        Dim sun_true_long As Double = mean + (1.916 * Math.Sin(mean * D2R)) + (0.02 * Math.Sin(2 * mean * D2R)) + 282.634
        If (sun_true_long > 360) Then
            sun_true_long = sun_true_long - 360
        ElseIf (sun_true_long  360) Then
            right_ascension = right_ascension - 360
        ElseIf (right_ascension  1) Then
            Response.Write("Sun never rises on this day. " & input_date.Year & "/" & input_date.Month & "/" & input_date.Day & "
") ElseIf (cosH < -1) Then Response.Write("Sun never sets on this day. " & input_date.Year & "/" & input_date.Month & "/" & input_date.Day & "
") End If 'Calculate and convert into hour of sunset or sunrise Dim hour As Double = 0 If (set_rise = 1) Then hour = 360 - R2D * Math.Acos(cosH) ElseIf (set_rise = 0) Then hour = R2D * Math.Acos(cosH) End If hour = hour / 15 'Calculate local mean time of rising or setting Dim local_mean_time As Double = hour + right_ascension - (0.06571 * t) - 6.622 'Adjust time to UTC Dim utc As Double = local_mean_time - long_hour 'Convert time from UTC to local time zone Dim local_time As Double = utc + offset If (local_time > 24) Then local_time = local_time - 24 ElseIf (local_time < 0) Then local_time = local_time + 24 End If 'Convert the local_time into time format Dim s_hour As Integer = Math.Floor(local_time) Dim s_minute As Integer = Math.Floor((local_time - s_hour) * 60) If (s_minute < 10) Then Return s_hour & ":0" & s_minute Else Return s_hour & ":" & s_minute End If End Function

Retrieving data

  Protected Function Get_Data(ByVal query As String)
    Dim result As String
    result = ""
    'Database connection string. Replace lower capital with MySQL database settings
    Using Con As New MySqlConnection("Database=database;Server=server;User ID=userid;Password=password;ignore prepare=false")
    Con.Open()
    Using Com As New MySqlCommand(query, Con)
    Com.CommandType = Data.CommandType.Text
    Using RDR = Com.ExecuteReader()
       If RDR.HasRows Then
          RDR.Read()
          result = RDR.Item("time_zone").ToString() & "," & RDR.Item("country_code") & "," & RDR.Item("latitude") & "," & RDR.Item("longitude")
        End If
     End Using
     End Using
     End Using
     Return result
 End Function

Converting IP address to IP number

Protected Function ip_to_number(ByVal ip_addr As String)
  Dim ip_block() As String
  Dim ip_num As Long

  ip_block = Split(ip_addr, ".")
  ip_num = (ip_block(0) * (256 ^ 3)) + (ip_block(1) * (256 ^ 2)) + (ip_block(2) * 256) + ip_block(3)

  Return ip_num
End Function

Calculation for sunrise and sunset time

	Protected Function calculate(input_date As DateTime, set_rise As Integer, offset As Double)
	'Convert longitude into hour value
	Dim long_hour As Double = longitude / 15
	Dim t As Double

	'sunset = 0, sunrise = 1
	'calculate approximate time
	If (set_rise = 1) Then
		t = input_date.DayOfYear + ((6 - long_hour) / 24)
	ElseIf (set_rise = 0) Then
		t = input_date.DayOfYear + ((18 - long_hour) / 24)
	End If

	'Calculate Sun's mean anomaly time
	Dim mean As Double = (0.9856 * t) - 3.289

	'Calculate Sun's true longitude
	Dim sun_true_long As Double = mean + (1.916 * Math.Sin(mean * D2R)) + (0.02 * Math.Sin(2 * mean * D2R)) + 282.634
	If (sun_true_long > 360) Then
		sun_true_long = sun_true_long - 360
	ElseIf (sun_true_long  360) Then
		right_ascension = right_ascension - 360
	ElseIf (right_ascension  1) Then
		Response.Write("Sun never rises on this day. " & input_date.Year & "/" & input_date.Month & "/" & input_date.Day & "
") ElseIf (cosH < -1) Then Response.Write("Sun never sets on this day. " & input_date.Year & "/" & input_date.Month & "/" & input_date.Day & "
") End If 'Calculate and convert into hour of sunset or sunrise Dim hour As Double = 0 If (set_rise = 1) Then hour = 360 - R2D * Math.Acos(cosH) ElseIf (set_rise = 0) Then hour = R2D * Math.Acos(cosH) End If hour = hour / 15 'Calculate local mean time of rising or setting Dim local_mean_time As Double = hour + right_ascension - (0.06571 * t) - 6.622 'Adjust time to UTC Dim utc As Double = local_mean_time - long_hour 'Convert time from UTC to local time zone Dim local_time As Double = utc + offset If (local_time > 24) Then local_time = local_time - 24 ElseIf (local_time < 0) Then local_time = local_time + 24 End If 'Convert the local_time into time format Dim s_hour As Integer = Math.Floor(local_time) Dim s_minute As Integer = Math.Floor((local_time - s_hour) * 60) If (s_minute < 10) Then Return s_hour & ":0" & s_minute Else Return s_hour & ":" & s_minute End If End Function

Retrieving data

	Protected Function Get_Data(ByVal query As String)
	Dim result As String
	result = ""
	'Database connection string. Replace lower capital with MySQL database settings
	Using Con As New MySqlConnection("Database=database;Server=server;User ID=userid;Password=password;ignore prepare=false")
		Con.Open()
		Using Com As New MySqlCommand(query, Con)
			Com.CommandType = Data.CommandType.Text
			Using RDR = Com.ExecuteReader()
				If RDR.HasRows Then
					RDR.Read()
					result = RDR.Item("time_zone").ToString() & "," & RDR.Item("country_code") & "," & RDR.Item("latitude") & "," & RDR.Item("longitude")
				End If
			End Using
		End Using
	End Using
	Return result
End Function

Converting IP address to IP number

Protected Function ip_to_number(ByVal ip_addr As String)
	Dim address As System.Net.IPAddress
	Dim ipnum As System.Numerics.BigInteger

	If System.Net.IPAddress.TryParse(ip_addr, address) Then
		Dim addrBytes() As Byte = address.GetAddressBytes()

		If System.BitConverter.IsLittleEndian Then
			Dim byteList As New System.Collections.Generic.List(Of Byte)(addrBytes)
			byteList.Reverse()
			addrBytes = byteList.ToArray()
		End If

		If addrBytes.Length > 8 Then
			'IPv6
			ipnum = System.BitConverter.ToUInt64(addrBytes, 8)
			ipnum <<= 64
			ipnum += System.BitConverter.ToUInt64(addrBytes, 0)
		Else
			'IPv4
			ipnum = System.BitConverter.ToUInt32(addrBytes, 0)
		End If
		Return ipnum
	End If
End Function

Was this article helpful?

Related Articles