[Django] 날짜 조건별로 테이블에 저장된 첫번째 값 / 마지막 값 구하기

from datetime import datetime
from dateutil.relativedelta import relativedelta

start_year = 2020
end_year = 2023

for year in range(start_year, end_year + 1):
    for month in range(1, 13):
        # 해당 월의 첫 번째 날짜 계산
        start_date = datetime(year, month, 1)
        
        # 해당 월의 다음 달 첫 번째 날짜 계산
        next_month = start_date + relativedelta(months=1)
        
        # 다음 달 첫 번째 날짜에서 하루를 빼고, 이를 해당 월의 마지막 날짜로 설정
        end_date = next_month - relativedelta(days=1)
        
        query = "SELECT time FROM your_table WHERE year = ? AND month = ? AND day >= ? AND day <= ? ORDER BY time ASC LIMIT 1;"
        prepared_query = session.prepare(query)
        result = session.execute(prepared_query, [year, month, start_date.day, end_date.day])
        
        if result.one() is None:
            continue
        
        first_date = result.one().time
        break
    
    if first_date:
        break
        
        
for year in range(end_year, start_year - 1, -1):
    for month in range(12, 0, -1):
        start_date = datetime(year, month, 1)
        next_month = start_date + relativedelta(months=1)
        end_date = next_month - relativedelta(days=1)
        
        query = "SELECT time FROM your_table WHERE year = ? AND month = ? AND day >= ? AND day <= ? ORDER BY time DESC LIMIT 1;"
        prepared_query = session.prepare(query)
        result = session.execute(prepared_query, [year, month, start_date.day, end_date.day])
        
        if result.one() is None:
            continue
        
        last_date = result.one().time
        break
    
    if last_date:
        break

print("First date:", first_date)
print("Last date:", last_date)

만약 db에 30만건 이상 데이터가 있는 경우,

데이터가 일정 주기로 insert되지 않는 경우 구하는 방법

 

-- month 기준

from datetime import datetime

start_year = 2020
end_year = 2023

for year in range(start_year, end_year + 1):
    for month in range(1, 13):
        query = "SELECT time FROM your_table WHERE year = ? AND month = ? ORDER BY time ASC LIMIT 1;"
        prepared_query = session.prepare(query)
        result = session.execute(prepared_query, [year, month])
        
        if result.one() is None:
            continue
        
        first_date = result.one().time
        break
    
    if first_date:
        break

for year in range(end_year, start_year - 1, -1):
    for month in range(12, 0, -1):
        query = "SELECT time FROM your_table WHERE year = ? AND month = ? ORDER BY time DESC LIMIT 1;"
        prepared_query = session.prepare(query)
        result = session.execute(prepared_query, [year, month])
        
        if result.one() is None:
            continue
        
        last_date = result.one().time
        break
    
    if last_date:
        break

print("First date:", first_date)
print("Last date:", last_date)

 

 

-- day 기준