Django + Postgres Timezones

Question

I'm trying to figure out what's going on with a timezone conversion that's happening in Django.

My view code is as below, it filters on a date range and groups on the day of creation:

def stats_ad(request):
      start_date = datetime.datetime.strptime(request.GET.get('start'), '%d/%m/%Y %H:%M:%S')
      end_date = datetime.datetime.strptime(request.GET.get('end'), '%d/%m/%Y %H:%M:%S')
      fads = Ad.objects.filter(created__range=[start_date, end_date]).extra(select={'created_date': 'created::date'}).values('created_date').annotate(total=Count('id')).order_by("created_date")

The SQL query that is produced by django when I set the get variable of start to "01/05/2013 00:00:00" and the request end variable to "11/05/2013 23:59:00":

SELECT (created::date) AS "created_date", COUNT("ads_ad"."id") AS "total" FROM "ads_ad" WHERE "ads_ad"."created" BETWEEN E'2013-05-01 00:00:00+10:00' and E'2013-05-11 23:59:59+10:00' GROUP BY created::date, (created::date) ORDER BY "created_date" ASC

If I manually run that on my Postgresql database, it's all good, finds the following:

created_date  total
2013-05-10    22
2013-05-11    1

However If I do the following:

for a in fads:
        recent_ads.append({"dates": a['created_date'].strftime('%d/%m/%Y'), 'ads': a['total']})

It gives me the following output:

[{"dates": "09/05/2013", "ads": 1}, {"dates": "10/05/2013", "ads": 22}]

I'm at a loss at why it's changed the dates?

Anyone got any ideas?

Cheers, Ben

Joe Jasinski · Accepted Answer · 2013-05-13 04:44:09Z

1

Just a through on this. As of Django 1.4, Django now supports timezone aware dates and times. Perhaps it's possible that a conversion between your local timezone and the timezone that the data is stored in (possibly GMT) is taking place at some point. Perhaps that difference crosses the international date line, in which case the dates may show up differently.

Django has an interesting section describing the new timezone support feature.
https://docs.djangoproject.com/en/1.4/topics/i18n/timezones/

That's what came to mind, anyway, when you described your problem. Hope this helps.

answered May 13, 2013 at 4:44

Joe Jasinski

10.9k18 gold badges77 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paulo Scardine · Accepted Answer · 2013-05-13 04:47:04Z

1

Python datetime from the standard python library is a mess.

Probably you are creating naive datetime instances (instances that lack timezone information).

# naive
now = datetime.datetime.now()

# TZ aware
from django.utils.timezone import utc
now = datetime.datetime.utcnow().replace(tzinfo=utc)

In recent Django, datetime storage is always offset-aware, so you better convert naive datetimes - otherwise an automatic (and sometimes wrong) conversion will take place.

Take a look at the docs about Django Time Zones.

edited May 13, 2013 at 4:47

answered May 13, 2013 at 4:41

Paulo Scardine

78.2k12 gold badges134 silver badges153 bronze badges

5 Comments

Ben Kilah Over a year ago

Any idea how I would do this in my code aboe? I've tried adding the "replace(tzinfo=utc)" to the clause that creates the datetime object, still the same results altho the warning about naive datetimes is gone from the console.. it seems to be on the output.

Paulo Scardine Over a year ago

You probably should use your real timezone instead of utc, like datetime.datetime.utcnow().replace(tzinfo=pytz.timezone('Israel'))

Ben Kilah Over a year ago

done, samething tho, is there way to apply that to the output? the "a['created_date'].strftime('%d/%m/%Y')"?

Paulo Scardine Over a year ago

@BenKilah: as output you mean inside a template? look at docs.djangoproject.com/en/dev/topics/i18n/timezones/…

Ben Kilah Over a year ago

sorry I mean access the variable in the view as per the "for a in fads" code above, that's where it's doing a conversion by the looks of it for some reason.

Collectives™ on Stack Overflow

Django + Postgres Timezones

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related