Stop Django translating times to UTC

Question

Timezones are driving me crazy. Every time I think I've got it figured out, somebody changes the clocks and I get a dozen errors. I think I've finally got to the point where I'm storing the right value. My times are timestamp with time zone and I'm not stripping the timezone out before they're saved.

TIME_ZONE = 'Europe/London'
USE_I18N = USE_L10N = USE_TZ = True

Here's a specific value from Postgres through dbshell:

=> select start from bookings_booking where id = 280825;
2019-04-09 11:50:00+01

But here's the same record through shell_plus

Booking.objects.get(pk=280825).start
datetime.datetime(2019, 4, 9, 10, 50, tzinfo=<UTC>)

DAMMIT DJANGO, IT WASN'T A UTC TIME!

These times work fine in templates/admin/etc but when I'm generating PDF and spreadsheet reports, this all goes awry and I'm suddenly have to re-localise the times manually. I don't see why I have to do this. The data is localised. What is happening between the query going to the database and me getting the data?

I bump into these issues so often I have absolutely no confidence in myself here —something quite unnerving for a senior dev— so I lay myself at your feet. What am I supposed to do?

Ataghan

Note that database connections have their own timezone settings: "The PostgreSQL backend stores datetimes as timestamp with time zone. In practice, this means it converts datetimes from the connection’s time zone to UTC on storage, and from UTC to the connection’s time zone on retrieval." Django sets the database connection to UTC, while presumably your dbshell configuration uses something else. – Signora

Answer 1

You're interpreting this wrongly. The database stores a UTC time most of the time. If you use PostgreSQL, the database can store a time with time zone info, but for practical purposes (*) it's easiest to just think the time in your db is stored as UTC (i.e. as an absolute time that can be converted to any time zone) when USE_TZ = True. It always represents a correct point in time for which you don't need to remember or assume any timezone. And as far as I know, Django will always store the time as time-aware in UTC timezone.

So when you're fetching the time object using select in psql, you're getting back the time in your machine's local time zone (the time zone where you're running psql). If someone in "America/New_York" would run the same select query, she would see a -04 timestamp. Had the date been 2019-03-20, you'd have seen 2019-03-20 10:50:00+00 because on that date, Europe/London and UTC were the same.

When fetching the value of a DateTimeField as a python datetime.datetime object, Django always fetches the UTC value, because:

Dealing with aware datetime objects isn’t always intuitive. For instance, the tzinfo argument of the standard datetime constructor doesn’t work reliably for time zones with DST. Using UTC is generally safe; if you’re using other time zones, you should review the pytz documentation carefully.

This makes it easier to work with these datetime objects in your python code: They're always UTC times.

If you want to print these values in a PDF, use the same methods Django uses for the template rendering:

from django.utils import timezone
print(timezone.template_localtime(Booking.objects.get(pk=280825).start))

This renders the datetime in the default timezone (or if you activate() a different timezone, in the current timezone).

(*) Note: Why you should not give any meaning to the timezone saved in your db and just think about it as if it's all UTC: If you were to run servers in various timezones, you might actually end up saving timestamps in different timezones. They are still all correct (absolute timestamps) and can be converted to any other timezone. So basically the timezone used for saving is meaningless.

Datum

Seriously appreciate the explanation but I still have problems believing that a PG data type that calls itself "timestamp with time zone" does not actually keep the offset, and that's it's not Django being the thorn in my arse here. That all said, I'll get started on wrapping all my outputs in timezone.template_localtime. – Ataghan

To fuel that disbelief, I changed the timezone on my dev machine, restarted PG and I still get a +01 time back out. It really does not look like Postgres is converting all an internally-held UTC time to system-local. – Ataghan

Ok maybe I’m wrong in the output of psql. But the rest holds true. You can’t be sure which time zone is used when saving into the database, so Django always gives back a UTC datetime in the python objects. – Datum

Don't let me talk you out of it. Somebody on IRC just said the same thing. The PG docs say the same thing ("utc all the time") it's just frustrating when I think I've seen something else. It also spawns all sorts of small problems that makes me feel like I should have just stored naive datetimes all along. – Ataghan

It's really easy if you just think of it this way: the database saves a correct absolute time; Django always uses UTC-based times in the objects it passes around; when rendering, you use functions that convert to whatever timezone you want. – Datum

Answer 2

Please be aware that both Django and the Postgresql Database have their own timezone setting.

The Django timezone is set in the settings.py file:

TIME_ZONE = 'UTC'
USE_TZ = True

The Postgresql setting can be checked using:

SHOW TIMEZONE;

and set using:

SET TIMEZONE='UTC';

I'm not an expert on this, but I believe Django wants to store everything in UTC in the database and then convert to the Django timezone setting after it has been queried. On that basis I think you want to set the Postgresql timezone to be UTC then up to you if you change the Django setting to get the auto conversion or leave it as UTC and handle any conversions yourself in code.

Decare