Custom sql in django migration doesn't work

Question

I'm trying to run custom SQL in my migration. This is how it looks like:

from django.db import migrations

class Migration(migrations.Migration):

    dependencies = [
    ]

    operations = [
        migrations.RunSQL(
            "SET timezone TO 'UTC'",
        ),
    ]

When I run it with

./manage.py migrate helper

I can see in the logs that SQL command was run:

[17/Feb/2017 20:37:37] DEBUG [django.db.backends.schema:103] SET timezone TO 'UTC'; (params None)
[17/Feb/2017 20:37:37] DEBUG [django.db.backends:90] (0.000) SET timezone TO 'UTC';

But when I go and check it in database it still says timezone is localtime:

gdp=# show timezone;
 TimeZone  
-----------
 localtime
(1 row)

If I run the same command manually it works OK:

gdp=# SET timezone TO 'UTC';
SET
gdp=# 
gdp=# show timezone;
 TimeZone 
----------
 UTC
(1 row)

Django version: 1.10.5

PostgreSQL version: 9.5.5

Full logs:

[17/Feb/2017 20:37:37] DEBUG [django.db.backends:90] (0.591) CREATE EXTENSION IF NOT EXISTS postgis; args=None
[17/Feb/2017 20:37:37] DEBUG [django.db.backends:90] (0.002) 
            SELECT c.relname, c.relkind
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r', 'v')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid); args=None
[17/Feb/2017 20:37:37] DEBUG [django.db.backends.schema:103] CREATE TABLE "django_migrations" ("id" serial NOT NULL PRIMARY KEY, "app" varchar(255) NOT NULL, "name" varchar(255) NOT NULL, "applied" timestamp with time zone NOT NULL); (params None)
[17/Feb/2017 20:37:37] DEBUG [django.db.backends:90] (0.014) CREATE TABLE "django_migrations" ("id" serial NOT NULL PRIMARY KEY, "app" varchar(255) NOT NULL, "name" varchar(255) NOT NULL, "applied" timestamp with time zone NOT NULL); args=None
[17/Feb/2017 20:37:37] DEBUG [django.db.backends:90] (0.000) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=()
[17/Feb/2017 20:37:37] DEBUG [django.db.backends:90] (0.001) 
            SELECT c.relname, c.relkind
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r', 'v')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid); args=None
[17/Feb/2017 20:37:37] DEBUG [django.db.backends:90] (0.000) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=()
[17/Feb/2017 20:37:37] DEBUG [django.db.backends.schema:103] SET timezone TO 'UTC'; (params None)
[17/Feb/2017 20:37:37] DEBUG [django.db.backends:90] (0.000) SET timezone TO 'UTC'; args=None
[17/Feb/2017 20:37:37] DEBUG [django.db.backends:90] (0.000) 
            SELECT c.relname, c.relkind
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r', 'v')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid); args=None
[17/Feb/2017 20:37:37] DEBUG [django.db.backends:90] (0.000) INSERT INTO "django_migrations" ("app", "name", "applied") VALUES ('helper', '0001_initial', '2017-02-17T20:37:37.272476+00:00'::timestamptz) RETURNING "django_migrations"."id"; args=(u'helper', u'0001_initial', datetime.datetime(2017, 2, 17, 20, 37, 37, 272476, tzinfo=<UTC>))
[17/Feb/2017 20:37:37] DEBUG [django.db.backends:90] (0.001) 
            SELECT c.relname, c.relkind
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r', 'v')
                AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                AND pg_catalog.pg_table_is_visible(c.oid); args=None
[17/Feb/2017 20:37:37] DEBUG [django.db.backends:90] (0.000) SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations"; args=()

EDIT:

I have these settings:

USE_TZ = True 
TIME_ZONE = 'UTC'

but with this Django saves datetime to Postgres like this '2016-09-12T08:06:45-04:00' using my local timezone. It does convert it back to UTC when I query database through Django but I'd like to have 'clean' datetime in Postgres that is something like this '2016-09-12T12:06:45+00'.

NST · Accepted Answer · 2017-02-21 16:48:43Z

1

I found an answer to my question in PostgreSQL documentation 8.5.3. Time Zones:

The SQL command SET TIME ZONE sets the time zone for the session.

I was able to solve this by setting timezone in postgresql.conf (for me it was located in /etc/postgresql/9.5/main).

answered Feb 21, 2017 at 16:48

NST

75411 silver badges20 bronze badges

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

Comments

GammaSQ · Accepted Answer · 2017-02-17 20:57:13Z

0

Django sets its own timezone when you use a DateTimeField. In your settings.py, you should find this setting:

TIME_ZONE='UTC'

You can disable djangos timezone-usage by setting this to None. However, I believe that would simply convert all DateTimeField-values into values without a timezone. (didn't try it)

I recommend using djangos TIME_ZONE setting in any case, since it ensures consistency over all databases.

answered Feb 17, 2017 at 20:57

GammaSQ

4012 silver badges11 bronze badges

1 Comment

NST Over a year ago

Forgot to mention this (will edit my post): I have these settings: USE_TZ = True TIME_ZONE = 'UTC' but with this Django saves datetime to Postgres like this '2016-09-12T08:06:45-04:00' using my local timezone. It does convert it back to UTC when I query database through Django but I'd like to have 'clean' datetime in Postgres.

Collectives™ on Stack Overflow

Custom sql in django migration doesn't work

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related