SQLite's strftime() doesn't work properly when run from Python 3.13.2

[deathnoise]

Bug report

Bug description:

I'm trying to run an UPDATE on SQLITE database from inside Python script.
The exact same, verbatim SQL update query works perfectly fine in DBeaver.
When I copy it to Python - it doesn't work.
The UPDATE puts NULL on every row where it should put a specific, formatted date.
It looks like SQLITE's strftime() function just doesn't work, properly.

Column "Date" in database contains (before running UPDATE) SQLITE-compatible timestamps like so:
1439416800
etc

After running following UPDATE - the value on every row, in column "Date" should contain the formatted date, like so:
09/30/23
...
etc
(month,day,year)

minimal code is similar to this:

import sqlite3

test = """
UPDATE bu2 set "Date" = strftime('%m/%d/%g', datetime("Date"), 'unixepoch');
"""

cxn = sqlite3.connect('base.db')
cxn.execute(test)
cxn.commit()
cxn.close()

CPython versions tested on:

3.13

Operating systems tested on:

Windows

[erlend-aasland]

You are using SQLite's stftime function. This is not a Python API; it is an SQL function. Please see the SQLite docs for how to use strftime. You can also post to the SQLite forum if you need help with SQLite functions.

[brianschubert]

(in case helpful, %g is only available in sqlite 3.46.0+, and it looks like you mean to use datetime("Date", 'unixepoch'), not datetime("Date"), 'unixepoch'))

[deathnoise]

You are using SQLite's stftime function. This is not a Python API; it is an SQL function. Please see the SQLite docs for how to use strftime. You can also post to the SQLite forum if you need help with SQLite functions.

It is used correctly, the exactly same line used in DBeaver works on the same base.
I'm aware that I'm using strftime from SQLITE, but it doesn;'t work specifically in Python.

[brianschubert]

Like Erlend said, this is happening inside SQLite, so it’s unlikely to be a CPython bug. I’d advise following up on the SQLite forum as suggested, or opening a thread on https://discuss.python.org/c/help/7 if you need help with the Python interaction.

FWIW I’m guessing this has to do with the version of SQLite being used by your Python installation being different than what’s being used by DBeaver. The version I’m using doesn’t support the constructs in the issue:

sqlite> select sqlite_version();
('3.37.2',)

sqlite> select strftime('%g', 'now');
(None,)
sqlite> select strftime('%Y', 'now');
('2025',)

sqlite> select datetime(1439416800);
(None,)
sqlite> select datetime(1439416800, 'unixepoch');
('2025-08-12 22:00:00',)

[erlend-aasland]

The %g format specifier was introduced with SQLite 3.46.0. Python 3.13.2 shipped with 3.45.3¹, IIRC. Python 3.13.3 shipped with SQLite 3.49.1¹. IOW, if you are on Windows, upgrading your Python 3.13 version should fix your problem, @deathnoise.

Footnotes

1. for the Windows and macOS installers. ↩ ↩²