django orm queryset - how to perform sql query MIN/MAX FILTER

You can use the aggregate()--(doc) method or (annotate()--(doc) method) to get the result. For that, you must use the Min() and Max() database functions along with the filter parameter.

from django.db.models import Min, Q, Max

agg_response = TimeRecord.objects.aggregate(
    min_time_in=Min("created_at", filter=Q(activity_type=1)),
    max_time_out=Max("created_at", filter=Q(activity_type=2)),
)

The original query:

SELECT 
    created_at::date as created_date,
    company_id,
    employee_id,
    MIN(created_at::time) FILTER (WHERE activity_type = 1) as min_time_in,
    MAX(created_at::time) FILTER (WHERE activity_type = 2) as max_time_out
FROM
   timerecord
where date(created_at) = '2020-11-18' and employee_id = 1
GROUP BY created_date, company_id, employee_id
ORDER BY created_date, employee_id;

is an example of conditional/selective aggregation. The syntax above is available in PostgreSQL/SQLite.

It could be easily rewritten using CASE expression as:

SELECT 
    created_at::date as created_date,
    company_id,
    employee_id,
    MIN(CASE WHEN activity_type = 1 THEN created_at::time END)as min_time_in,
    MAX(CASE WHEN activity_type = 2 THEN created_at::time END) as max_time_out
FROM
   timerecord
where date(created_at) = '2020-11-18' and employee_id = 1
GROUP BY created_date, company_id, employee_id
ORDER BY created_date, employee_id;

db<>fiddle demo

Which is supported by any modern RDBMS and it is supported by Django:

conditional-aggregation

What if we want to find out how many clients there are for each account_type? We can nest conditional expression within aggregate functions to achieve this:

Client.objects.aggregate(
...     regular=Count('pk', filter=Q(account_type=Client.REGULAR)),
...     gold=Count('pk', filter=Q(account_type=Client.GOLD)),
...     platinum=Count('pk', filter=Q(account_type=Client.PLATINUM)),
... )

This aggregate produces a query with the SQL 2003 FILTER WHERE syntax on databases that support it

...

On other databases, this is emulated using a CASE statement