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 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 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 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 < 0) Then
            sun_true_long = sun_true_long + 360
        End If

        'Calculate Sun's right ascension
        Dim right_ascension As Double = R2D * Math.Atan(0.91764 * Math.Tan(sun_true_long * D2R))
        If (right_ascension > 360) Then
            right_ascension = right_ascension - 360
        ElseIf (right_ascension < 0) Then
            right_ascension = right_ascension + 360
        End If

        'Adjust right ascension value to be in the same quadrant as Sun's true longitude
        Dim Lquadrant As Double = (Math.Floor(sun_true_long / 90)) * 90
        Dim RAquadrant As Double = (Math.Floor(right_ascension / 90)) * 90
        right_ascension = right_ascension + (Lquadrant - RAquadrant)

        'Convert right ascension value into hours
        right_ascension = right_ascension / 15

        'Calculate Sun's declination
        Dim sinDec As Double = 0.39782 * Math.Sin(sun_true_long * D2R)
        Dim cosDec As Double = Math.Cos(Math.Asin(sinDec))

        'Setting Sun's zenith value
        Dim zenith As Double = 90 + (50 / 60)

        'Calculate Sun's local hour angle
        Dim cosH As Double = (Math.Cos(zenith * D2R) - (sinDec * Math.Sin(latitude * D2R))) / (cosDec * Math.Cos(latitude * D2R))

        If (cosH > 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 = (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

Download demo project : Display sunrise and sunset time.

 

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 < 0) Then
            sun_true_long = sun_true_long + 360
        End If

        'Calculate Sun's right ascension
        Dim right_ascension As Double = R2D * Math.Atan(0.91764 * Math.Tan(sun_true_long * D2R))
        If (right_ascension > 360) Then
            right_ascension = right_ascension - 360
        ElseIf (right_ascension < 0) Then
            right_ascension = right_ascension + 360
        End If

        'Adjust right ascension value to be in the same quadrant as Sun's true longitude
        Dim Lquadrant As Double = (Math.Floor(sun_true_long / 90)) * 90
        Dim RAquadrant As Double = (Math.Floor(right_ascension / 90)) * 90
        right_ascension = right_ascension + (Lquadrant - RAquadrant)

        'Convert right ascension value into hours
        right_ascension = right_ascension / 15

        'Calculate Sun's declination
        Dim sinDec As Double = 0.39782 * Math.Sin(sun_true_long * D2R)
        Dim cosDec As Double = Math.Cos(Math.Asin(sinDec))

        'Setting Sun's zenith value
        Dim zenith As Double = 90 + (50 / 60)

        'Calculate Sun's local hour angle
        Dim cosH As Double = (Math.Cos(zenith * D2R) - (sinDec * Math.Sin(latitude * D2R))) / (cosDec * Math.Cos(latitude * D2R))

        If (cosH > 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 = (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

Download demo project : Display sunrise and sunset time.

 


Do you like this article? Share it with others by clicking the social media buttons below. We will write more articles related to this topic.