sqlite

Conversion between text and julian days in SQLITE is one day off

When I convert a text date to Julian, and then convert from Julian back to text, the result is one day off.

SELECT CAST(julianday('2025-08-17') AS INTEGER);
2460904
SELECT strftime('%Y-%m-%d',2460904);
2025-08-16

Why is the text representation of the date one day earlier than the original date even though I use the same Julian value 2460904?

Solution

  • The comment above by @mechanical_meat basically answers the question, but I am providing a formal answer here as well.

    If you inspect the value returned by julianday() before you cast to integer, you will see the issue here:

    SELECT julianday('2025-08-17')
-- 2460904.5

    For whatever reason, the cast to integer ends up rounding down to 2460904, which is one day earlier. But if you feed the output from julianday() directly into strftime(), you get the correct/expected behavior:

    SELECT strftime('%Y-%m-%d', julianday('2025-08-17'));
--  2025-08-17