[SOLVED] How to sum two datefields in sqlite using django orm

How to sum two datefields in sqlite using django orm

My django5 model has two datetime fields, bgg_looked_at and updated_at. They are nullable, but fields should not be null when this query is ran.

So far I tried

class UnixTimestamp(Func):
    function = 'strftime'
    template = "%(function)s('%%s', %(expressions)s)"  # Escape % with %%
    output_field = FloatField()  # Explicitly declare the output as FloatField

and

    listings = Listing.objects.annotate(
        bgg_looked_at_timestamp=Coalesce(
            UnixTimestamp(F('bgg_looked_at')),
            Value(0.0, output_field=FloatField())
        ),
        updated_at_timestamp=Coalesce(
            UnixTimestamp(F('updated_at')),
            Value(0.0, output_field=FloatField())
        )
    ).annotate(
        sum_fields=ExpressionWrapper(
            F('bgg_looked_at_timestamp') + F('updated_at_timestamp'),
            output_field=FloatField()
        )
    ).order_by('sum_fields')

but I get

TypeError: not enough arguments for format string

when I just do a len(listings)

Solution

Django performs a second interpolation on this, so the first interpolation will generate %s, and then format it with the parameters.

You can fix this with:

class UnixTimestamp(Func):
    function = 'strftime'
    template = "%(function)s('%%%%s', %(expressions)s)"
    output_field = FloatField()

That being said, I don't think it makes much sense to sum up two timestamps. It is like adding two positions, you should add a position with a vector (time delta), not a second timestamp.