1

I am trying to query between a range of dates. Looking at the query that Django generates, I see that it is not taking the time from the datetime

This is my code

        tz = pytz.timezone('Europe/London')
        today = datetime.now(tz=tz)
        week_end = today.replace(hour=23,minute=59,second=59,microsecond=0)
        day_start = today - timedelta(6)
        week_start = day_start.replace(hour=0, minute=0, second=0, microsecond=0)

        ultimas_transferencias = Transferencia.objects.filter(fecha__gte=week_start,
                                                              fecha__lte=week_end, estatus__id=1)\
            .extra(select={'dia': 'DAY(fecha)'}).order_by().values('dia') \
            .annotate(total=Sum('monto')).values('dia', 'total').annotate(numero=Count('pk')).values('dia', 'total','numero')
        print(ultimas_transferencias.query)
        return JsonResponse({'data': list(ultimas_transferencias)})

And this is the query:

SELECT (DAY(fecha)) AS `dia`, SUM(`remesasapp_transferencia`.`monto`) AS `total`, COUNT(`remesasapp_transferencia`.`id`) AS `numero` 
FROM `remesasapp_transferencia` 
WHERE (`remesasapp_transferencia`.`estatus_id` = 1 AND 
`remesasapp_transferencia`.`fecha` >= 2020-08-23 23:00:00 
AND `remesasapp_transferencia`.`fecha` <= 2020-08-30 22:59:59) GROUP BY (DAY(fecha)) ORDER BY NULL

Notice that the time comes out as 2020-08-23 23:00:00 instead of 2020-08-23 00:00:00

  • Django stores all times as UTC in the database, so you should account for that when querying. The 1 hour difference might be daylight saving time. – Lucas Moeskops Aug 30 '20 at 11:49
  • Thanks @LucasMoeskops ! You were right, I used timezone.now() instead datetime.now() and it worked. –  Aug 30 '20 at 12:00

1 Answers1

0

I found the solution. Instead of today = datetime.now(tz=tz) I used today= timezone.now()