Normalize dates and annotate Django ORM transactions with day-level fiat prices using Subquery and Coalesce

Enrich Django ORM transactions with day-level fiat prices: normalize string dates via data migrations, then annotate querysets using Subquery and Coalesce.

Enriching transactional data with day-level fiat prices is a common need in analytics-heavy Django projects. The challenge becomes more interesting when the application contains both a normalized table with real datetime values and a legacy table where dates are stored as strings. The goal is to perform the enrichment at the queryset level, not in Python, and to keep it fast, consistent, and maintainable.

The baseline Python approach

The logic below iterates over transactions, locates a corresponding day price from Price, falls back to PriceTextBackup if necessary, and aggregates values. It works, but it lives in Python loops and does not leverage the database for joining and annotating rows.

for entry in transactions:
    day_marker = entry.timestamp.date()
    rate_obj = Price.objects.filter(fiat=cur_id, date=day_marker).first()
    if not rate_obj:
        rate_obj = PriceTextBackup.objects.filter(
            fiat=cur_id,
            date=day_marker.strftime("%d-%m-%Y")
        ).first()
    if rate_obj:
        if entry.amount > 0:
            subtotal = rate_obj.price * entry.amount
            price_list.append(subtotal)
            transaction_counter += entry.amount

A fragment from the template shows how the enriched fiat value is displayed with a hover popup:

{% if row.amount >= 0 %}
    <td onmouseover="showPopup({{ forloop.counter }}0000)"
        onmouseout="hidePopup()"
        class="text-success">{{ row.fiat_CHF|floatformat:2|intcomma }}</td>
{% else %}
    <td class="text-danger">{{ row.fiat_CHF|floatformat:2|intcomma }}</td>
{% endif %}

Why the ORM queryset approach fails as-is

The blocker is data shape. Price.date is a DateTimeField, while PriceTextBackup.date is a string. As long as one side is a CharField with a formatted date like %d-%m-%Y, you cannot reliably annotate Transactions with a day price using pure ORM expressions. Matching timestamps to per-day prices also requires comparing only the date part. Until dates are normalized, every queryset solution becomes clumsy or slow.

Migrate first: two safe paths

The cleanest way forward is to fix the data model. Either move usable rows from PriceTextBackup into Price, or convert the backup table’s date to a real temporal type. Both options are straightforward with Django’s data migrations.

Option 1: move usable rows from PriceTextBackup into Price

Create an empty migration and populate Price with rows that can be parsed from the backup. This retains Price as the single source of truth for day prices.

manage.py makemigrations --empty my_app

Then implement the migration like this:

# Generated by Django 5.2.0 on 2025-09-04 17:28

from django.db import migrations, models
from django.db.models import DateTimeField
from datetime import datetime


def seed_forward(apps, schema_editor):
    Price = apps.get_model('app_name', 'Price')
    PriceTextBackup = apps.get_model('app_name', 'PriceTextBackup')
    present = {
        (p.date.date(), p.fiat_id) for p in Price.objects.only('date', 'fiat_id')
    }
    stash = []
    for rec in PriceTextBackup.objects.all():
        parsed = None
        try:
            parsed = datetime.strptime(rec.date, '%d-%m-%Y').date()
        except ValueError:
            print('problem for {item.date!r}')
            continue
        key = (parsed, rec.fiat_id)
        if key not in present:
            present.add(key)
            stash.append(
                Price(price=rec.price, date=parsed, fiat_id=rec.fiat_id)
            )
        Price.objects.bulk_create(stash)


class Migration(migrations.Migration):

    dependencies = [
        ('app_name', '1234_previous_migration_file'),
    ]

    operations = [migrations.RunPython(seed_forward)]

Run this first on a copy of the database to validate the result. After this step, your queries can work against the single Price table.

Option 2: convert the backup date to a real DateTimeField

Alternatively, normalize the date on PriceTextBackup by migrating the string field to a DateTimeField. Start by changing the model:

from datetime import datetime


class PriceTextBackup(models.Model):
    date = models.DateTimeField(default=datetime(1970, 1, 1))
    price = models.FloatField()
    fiat = models.ForeignKey(Currencies, on_delete=models.DO_NOTHING)

Create the migration but don’t apply it yet:

python manage.py makemigrations

Then adapt the generated migration to parse legacy strings into a temporary field, swap fields, and keep the parsed result:

# Generated by Django 5.2.0 on 2025-09-04 17:28

from django.db import migrations, models
from django.db.models import DateTimeField
from datetime import datetime


def convert_forward(apps, schema_editor):
    PriceTextBackup = apps.get_model('app_name', 'PriceTextBackup')
    buffer = []
    for row in PriceTextBackup.objects.iterator():
        buffer.append(row)
        row.date_as_date = datetime.strptime(row.date, '%d-%m-%Y')
        if len(buffer) > 100:
            PriceTextBackup.objects.bulk_update(
                buffer, fields=('date_as_date',)
            )
            buffer = []

    PriceTextBackup.objects.bulk_update(buffer, fields=('date_as_date',))


class Migration(migrations.Migration):

    dependencies = [
        ('app_name', '1234_previous_migration_file'),
    ]

    operations = [
        migrations.AddField(
            model_name='pricetextbackup',
            name='date_as_date',
            field=models.DateTimeField(
                blank=True, null=True, verbose_name='Date'
            ),
        ),
        migrations.RunPython(convert_forward),
        migrations.RemoveField(
            model_name='pricetextbackup',
            name='date',
        ),
        migrations.RenameField(
            model_name='currencyrates',
            old_name='date_as_date',
            new_name='date',
        ),
        migrations.AddField(
            model_name='pricetextbackup',
            name='date',
            field=models.DateTimeField(verbose_name='Date'),
        ),
    ]

As before, test this on a copy of the database. If certain string dates don’t follow %d-%m-%Y, you will need to parse those differently or discard unusable rows.

Querying with Subquery and Coalesce

Once prices are normalized, you can annotate transactions with their matching day price directly in the queryset. For a single source of truth in Price, annotate with a Subquery that looks up the day and currency and picks the first result:

from django.db.models import OuterRef, Subquery

currency_id = 1234

Transactions.objects.annotate(
    price=Subquery(
        Price.objects.filter(
            date__date=OuterRef('date__date'),
            fiat_id=currency_id,
        ).values('price')[:1]
    )
)

If you keep both tables and want a fallback, wrap two Subquery expressions with Coalesce so that the second source is used only when the first has no match:

from django.db.models import OuterRef, Subquery
from django.db.models.functions import Coalesce

currency_id = 1234

Transactions.objects.annotate(
    price=Coalesce(
        Subquery(
            Price.objects.filter(
                date__date=OuterRef('date__date'),
                fiat_id=currency_id,
            ).values('price')[:1]
        ),
        Subquery(
            PriceTextBackup.objects.filter(
                date__date=OuterRef('date__date'),
                fiat_id=currency_id,
            ).values('price')[:1]
        ),
    ),
)

The annotated attribute price is available on each Transaction instance returned by the queryset. It is not a database field; it is computed by the SELECT and can be rendered in templates the same way as regular fields.

Why this matters

Moving enrichment into the database layer eliminates Python loops over potentially large datasets and prevents N+1 query patterns. Normalizing time semantics also keeps comparisons consistent and makes it trivial to work with date parts or perform aggregates. Lastly, having a single authoritative table for day prices reduces code branching and removes ambiguity between sources.

Practical wrap-up

Start by normalizing your price data. Either copy valid rows from PriceTextBackup into Price with a data migration, or convert PriceTextBackup.date to a real DateTimeField, parsing the legacy strings. After that, annotate Transactions using Subquery, with an optional Coalesce fallback if both tables remain. The result is a clean, database-driven enrichment that you can use directly in your views and templates without additional per-row lookups.