Need a formula: Extracting Years from Seconds Since January 1, 0001 12:00 AM

Question

Input: # of seconds since January 1st, of Year 0001

Output: # of Full years during this time period

I have developed an algorithm that I do not think is the optimal solution. I think there should be a solution that does not involve a loop. See Code Block 1 for the algorithm which A) Determines the quantity of days and B) Iteratively subtracts 366 or 365 depending on Leap Years from the Day Total while incrementing the Year Total

It's not as simple as Dividing DayCount by 365.2425 and truncating, because we hit a failure point at on January 1, 0002 (31536000 Seconds / (365.2425 * 24 * 60 * 60)) = 0.99934.

Any idea on a non-looping method for extracting years from a quantity of seconds since January 1, 0001 12:00 AM?

I need to figure this out because I need a date embedded in a long (which stores seconds) so that I can track years out to 12+ million with 1-second precision.

Code block 1 - Inefficient Algorithm to get Years from Seconds (Including Leap Years)

        Dim Days, Years As Integer

        'get Days
        Days = Ticks * (1 / 24) * (1 / 60) * (1 / 60) 'Ticks = Seconds from Year 1, January 1

        'get years by counting up from the beginning
        Years = 0
        While True
            'if leap year
            If (Year Mod 4 = 0) AndAlso (Year Mod 100 <> 0) OrElse (Year Mod 400 = 0) Then
                If Days >= 366 Then 'if we have enough days left to increment the year
                    Years += 1
                    Days -= 366
                Else
                    Exit While
                End If
                'if not leap  year
            Else
                If Days >= 365 Then 'if we have enough days left to increment the year
                    Years += 1
                    Days -= 365
                Else
                    Exit While
                End If
            End If
        End While

        Return Years

Edit: My solution was to skip the memory savings of embedding a date within 8 bits and to store each value (seconds through years) in separate integers. This causes instant retrievals at the expense of memory.

Edit2: Typo in first edit (8bits)

Answer 1

If you need accuracy to the very second, you'll want a commercial-grade datetime package; it's just too complex to do accurately with a simple algorithm. For instance:

• Many people have noted that we have leap years every 4 years, but did you know that every year divisible by 100 but not by 400 is not a leap-year? This has caused issues even in large commercial products.
• Some countries do not observe daylight-savings, and the ones who do observe it do so at different times of the year. This changes arbitrarily from year-to-year.
• Even in countries with DST, some states/cities arbitrarily choose not to use it.
• Years before 1582 use a slightly different calendar
• There were only 355 days in the year 1582 (or 354 in the year 1752, depending on the country).
• There are major issues when countries switch timezones. Some countries even lose entire days!
• Then there's leap-seconds. Some governing body decides arbitrarily whether or not we should add one (or sometimes, two) seconds to the clock each year. There's no way to know ahead of time when we'll have the next leap-second, and no pattern to past leap-seconds.

Because of these and more complications, you are better off not writing the code yourself, unless you can relax the constraint that you need accuracy to the very second over 12-million years.

"October 4, 1582 – Saint Teresa of Ávila dies. She is buried the next day, October 15."

Answer 2

Wikipeda has an article on Julian Date with an algorithm which you could adapt to your needs.

Answer 3

Const TICKS_PER_YEAR As Long = 315360000000000
Function YearsSinceBeginningOfTimeUntil(ByVal d As DateTime) As Integer
    Return Math.Floor(d.Ticks / TICKS_PER_YEAR)
End Function

Answer 4

You don't need a loop, calculate seconds from 1 Jan 0001 to unix epoch start (1 Jan 1970 00:00:00), and save somewhere. Then subtract it from your input, then use any tool available to convert unix timestamp (seconds from 1 Jan 1970) to years, then add 1970. I don't know much VB programming to post a detailed guide.