SQLite strftime() Leap Day Bug in Proleptic Gregorian Calendar

Issue Overview: Incorrect Leap Day Calculation in Proleptic Gregorian Calendar

The core issue revolves around SQLite’s handling of leap days in the proleptic Gregorian calendar, particularly for dates before the official adoption of the Gregorian calendar in 1582. SQLite uses the proleptic Gregorian calendar, which extends the Gregorian rules backward in time before its introduction. However, a bug was discovered in the strftime() function when converting Julian Day Numbers (JDN) to dates in the proleptic Gregorian calendar. Specifically, the function incorrectly returns February 29 for years that should not have a leap day under the Gregorian rules, such as the year 300 AD.

The bug manifests when converting Julian Day Numbers to dates using the strftime() function. For example, the Julian Day Number 1830692.5 corresponds to March 1, 300 AD, but SQLite incorrectly returns February 29, 300 AD. This discrepancy arises because SQLite’s internal computeYMD() function, which converts Julian Day Numbers to year-month-day values, does not correctly handle the leap year rules for dates before 1582. The issue is particularly pronounced for dates around the transition from February to March in years that are multiples of 100 but not multiples of 400 (e.g., 300 AD, 700 AD, etc.).

The problem is rooted in the implementation of Meeus’ algorithm in SQLite’s src/date.c file. Meeus’ algorithm is designed to handle dates in the Gregorian calendar, but it assumes that the Gregorian calendar rules are only applied to dates after October 15, 1582. SQLite, however, applies these rules proleptically to all dates, including those before 1582. This leads to incorrect calculations for leap years in the proleptic Gregorian calendar, especially for dates before 1582.

Possible Causes: Meeus’ Algorithm and Negative Year Handling

The primary cause of the bug lies in the implementation of Meeus’ algorithm in the computeYMD() function within SQLite’s src/date.c file. Meeus’ algorithm is used to convert Julian Day Numbers to year-month-day values, but it was not designed to handle negative years or dates before the Gregorian calendar’s introduction. Specifically, the algorithm uses a variable A to calculate the number of leap years since a reference point. The calculation of A involves dividing a value by 36524.25 and casting the result to an integer. However, the casting operation in C rounds toward zero, which is incorrect for negative values. Meeus’ algorithm expects the result to be rounded down (i.e., floored), but the C cast operation rounds toward zero, leading to incorrect results for dates before 1582.

Another contributing factor is the handling of negative numbers in the computeYMD() function. The function uses the expression A = (int)((Z - 1867216.25)/36524.25) to calculate the number of leap years. For positive values of Z, this works correctly, but for negative values, the cast to int rounds toward zero instead of flooring the result. This causes the function to incorrectly calculate the number of leap years for dates before 1582, leading to the incorrect leap day calculation.

Additionally, the computeYMD() function does not account for the fact that the Gregorian calendar was not in use before 1582. The proleptic Gregorian calendar applies the Gregorian leap year rules to all dates, including those before 1582. However, the computeYMD() function does not correctly handle the transition from the Julian calendar to the Gregorian calendar, leading to incorrect results for dates before 1582.

Troubleshooting Steps, Solutions & Fixes: Correcting Meeus’ Algorithm and Implementing Flooring

To address the issue, several changes were made to the computeYMD() function in SQLite’s src/date.c file. The primary fix involved modifying the calculation of the variable A to ensure that it always rounds down, regardless of whether the input is positive or negative. This was achieved by replacing the cast to int with a custom flooring function. The new calculation for A is as follows:

A = (int)((Z + 32044.75)/36524.25) - 52;

This change ensures that the result is always floored, even for negative values of Z. The addition of 32044.75 and the subtraction of 52 are necessary to align the calculation with the proleptic Gregorian calendar and ensure that the leap year calculation is correct for all dates.

In addition to the change in the calculation of A, the computeYMD() function was modified to handle negative years correctly. The function now uses a custom flooring function to ensure that all calculations are performed on positive numbers, even for dates before 1582. This ensures that the leap year calculation is correct for all dates, regardless of whether they are in the proleptic Gregorian calendar or the Julian calendar.

The fix was tested extensively by converting Julian Day Numbers to dates and verifying that the results were correct for all dates from JD 1 to JD 2500. The test cases included dates around the transition from February to March in years that are multiples of 100 but not multiples of 400 (e.g., 300 AD, 700 AD, etc.). The results confirmed that the fix correctly handles leap days in the proleptic Gregorian calendar and produces the correct results for all dates.

In addition to the fix for the leap day calculation, the strftime() function was also updated to handle additional conversion specifiers from the current definition of C strftime() in IEEE Std 1003.1-2024. This includes support for conversion specifiers such as %k, %l, and %P, which were previously not supported in SQLite. The documentation for the strftime() function was also updated to clarify the behavior of the %e, %k, and %l conversion specifiers, which now correctly describe the behavior of leading spaces for single-digit values.

Finally, the fix was checked into the SQLite source code repository and is available in the latest version of SQLite. Users who encounter issues with leap day calculations in the proleptic Gregorian calendar are encouraged to update to the latest version of SQLite to ensure that the fix is applied. The fix has been thoroughly tested and is expected to resolve all issues related to leap day calculations in the proleptic Gregorian calendar.

In conclusion, the bug in SQLite’s strftime() function was caused by incorrect handling of leap days in the proleptic Gregorian calendar, particularly for dates before 1582. The fix involved modifying the computeYMD() function to correctly handle negative years and ensure that all calculations are performed on positive numbers. The fix has been thoroughly tested and is available in the latest version of SQLite. Users who encounter issues with leap day calculations are encouraged to update to the latest version of SQLite to ensure that the fix is applied.