CODEWITHSUNDEEP
djangopostgresqltimezone
Tom
Tom

Reputation: 22841

Django Timezone Handling with Postgres

I recently upgraded a Django project from 1.3 to 1.5 in order to start using the timezone handling. Because I am a moron, my Django timezone was set to "America/NewYork" instead of using UTC. Turning Django's timezone support on automatically sets Postgres to UTC. Now I'm running into issues with running direct SQL queries. I can't seem to get the timezone filtering correct. Here's what I'm doing:

  1. Accepting a timestamp field from a user form
  2. Swapping that to UTC in my view with stamp.astimezone(timezone('UTC'))
  3. Passing that as a parameter (start.strftime('%Y-%m-%d %H:%M:00%z')) in a raw SQL query using a django.db.connection's cursor

The query that gets executed (logging to the console) looks correct:

SELECT to_char(created, 'YYYY-MM-DD HH12:MIam'),
COALESCE(heat_flow_1, 0.0) / 1000.0, COALESCE(heat_flow_2, 0.0) / 1000.0,
COALESCE(heat_flow_3, 0.0) / 1000.0
FROM results_flattenedresponse
WHERE installation_id = '66'
AND created BETWEEN TIMESTAMPTZ '2013-04-26 13:00:00+0000' AND TIMESTAMPTZ '2013-04-26 16:00:00+0000'

if I copy and paste that into PGAdmin, I get what I'm looking for, a set of rows starting at 9am EDT. However, the dataset that comes back from the django.db.connection's cursor has all of the dates pushed forward 4 hours (the difference between EDT and UTC). The rest of the data is actually correct, but the dates are being treated as UTC and pushed to the user's active timezone (even if I call deactivate). I feel like I have a mess of bad ideas wired together trying to fix this now, so I'm not sure what parts are good ideas and what are bad.

EDIT/ UPDATE

I've been trying a number of other things here and still getting bad results, but only when I query the database directly. The stuff in steps 2 and 3 above seems immaterial. The one quick fix I can see is actually setting the timezone in the query, e.g., SET timezone='America/New_York'; and then undoing it but that seems like a very bad idea for data integrity.

The other strange bit: I've set the Django timezone setting to UTC, but when I download a local copy and look at the data it's still marked as if it were set to America/New_York. I don't know if that's due to a setting on my local server or if there's a bug where the data isn't being localized properly by Django when it gets inserted into the second (non-default) database, though if that were the case I expect my problem would have gone away.

Upvotes: 1

Views: 1935

Answers (1)

Tom
Tom

Reputation: 22841

Since the server is now in UTC time, the created comes back as UTC by default, so the first bit needs to be

SELECT to_char(created AT TIME ZONE %s, 'YYYY-MM-DD HH12:MIam')

and pass in the timezone you want to see. This isn't the best approach in the world, but it works for me here because the queries are all run inside an object that has the relevant timezone as a property.

Upvotes: 1

Related Questions