CODEWITHSUNDEEP
pythondjango
Ben Kilah
Ben Kilah

Reputation: 3475

Django + Postgres Timezones

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

Upvotes: 1

Views: 4796

Answers (2)

Paulo Scardine
Paulo Scardine

Reputation: 77251

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.

Upvotes: 1

Joe Jasinski
Joe Jasinski

Reputation: 10791

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.

Upvotes: 1

Related Questions