SQLite database structure

[Depechie]

Is there a more detailed schema available of the db relationships?
And what is the query structure to get the same species count as what the dashboard shows per day?

I'm trying out things like

select results.species as species, results.confidence as confidence
from daily_events
    inner join hourly_weathers on daily_events.id = hourly_weathers.daily_events_id
    inner join results on hourly_weathers.id = results.id
where daily_events.date = '2025-03-06' and results.confidence >= 0.006
group by results.species
order by species asc

and

select DISTINCT common_name
from notes
where notes.date = '2025-03-06'
and confidence >= 0.8
order by common_name asc

But not getting the same data as the web UI, so any insights would be great :)

[tphakala]

To get daily species of February 15th

SELECT 
    common_name, 
    MAX(scientific_name) as scientific_name, 
    COUNT(*) as count
FROM 
    notes
WHERE 
    date = '2025-02-15' AND confidence >= 0.1
GROUP BY 
    common_name
ORDER BY 
    count DESC;

And to get hourly values for common_name species kurki (common crane)

SELECT 
    strftime('%H', time) as hour, 
    COUNT(*) as count
FROM 
    notes
WHERE 
    date = '2025-02-15' AND common_name = 'kurki' AND confidence >= 0.1
GROUP BY 
    strftime('%H', time);

[Depechie]

@tphakala I now see the latest release and it mentions new summary API endpoints.
What I was actually trying to do, was indeed get daily summary out of the DB.

Does this mean the new release has this out of the box?

What my ultimate goal is, get something like this: https://dotnet.social/@Moaske@vivaldi.net/114093667726576386
But then for BirdNet as a daily summary.
The example above is done with https://www.tapmusic.net/
With the added ability to also post to mastodon of course ;)

[Depechie]

So if I update my docker compose to the latest, I should be able to just call the API?
As in, the endpoints are running?

What is the url? .../api ?

[Depechie]

Never mind, did the update and found the endpoints descriptions in the code! Nice :)
api/v2/detections

[Depechie]

The api works great! Already have a result with a photo collage!
Only thing I noticed, is that when running api/v2/analytics/species/daily?date=2025-03-07 all the birds will have a count of 1, I guess the counting in the GetDailySpeciesSummary is maybe off?

[tphakala]

This is now fixed in latest nightly build, can you test?

[Depechie]

Count result is correct with the current nightly tagged container