DateField timezone issue with get_or_create (duplicate key value violates unique constraint)

[Jure Erznožnik]

My model:

class AccessUseByDomain(models.Model):
    domain = models.IntegerField(verbose_name=_('Domain'), choices=settings.DOMAINS, null=True, blank=True)
    date = models.DateField(verbose_name=_('Date'), auto_now=True)
    no_access = models.IntegerField(verbose_name=_('Accesses'))

    class Meta:
        unique_together = [
                ['domain', 'date']  # Logic.consume_access
        ]

The code that leads to error:

        c, unused = AccessUseByDomain.objects.get_or_create(domain=device.hw_domain, date=timezone.now().date(),
                                                            defaults=dict(no_access=0))
        c.no_access += 1
        c.save()

Queries generated in this code block:

• {'sql': 'SELECT "my_team_accessusebydomain"."id", "my_team_accessusebydomain"."domain", "my_team_accessusebydomain"."date", "my_team_accessusebydomain"."no_access" FROM "my_team_accessusebydomain" WHERE ("my_team_accessusebydomain"."domain" = 0 AND "my_team_accessusebydomain"."date" = \'2017-07-27\'::date)', 'time': '0.001'}
• {'sql': 'SAVEPOINT "s140667396605696_x4"', 'time': '0.000'}
• {'sql': 'INSERT INTO "my_team_accessusebydomain" ("domain", "date", "no_access") VALUES (0, \'2017-07-28\'::date, 0) RETURNING "my_team_accessusebydomain"."id"', 'time': '0.003'}
• {'sql': 'ROLLBACK TO SAVEPOINT "s140667396605696_x4"', 'time': '0.000'}
• {'sql': 'RELEASE SAVEPOINT "s140667396605696_x4"', 'time': '0.000'}
• {'sql': 'SELECT "my_team_accessusebydomain"."id", "my_team_accessusebydomain"."domain", "my_team_accessusebydomain"."date", "my_team_accessusebydomain"."no_access" FROM "my_team_accessusebydomain" WHERE ("my_team_accessusebydomain"."domain" = 0 AND "my_team_accessusebydomain"."date" = \'2017-07-27\'::date)', 'time': '0.000'}

(please note the different date generated between selects and insert)

Relevant settings.py section:

LANGUAGE_CODE = 'en-gb'
TIME_ZONE = 'Europe/Ljubljana'
USE_I18N = True
USE_L10N = True
USE_TZ = True

When this happens:
I'm getting this error between midnight and 2am on the server (the code is not client-dependent, I hope). As it happens, the server is in CET timezone, which is +2 right now. Naturally, the code in question has to be run at least twice.

It would seem that either get or create part of the get_or_create does not do appropriate timezone adjustments for the date provided in the filter. My bet is on the get part (the date in INSERT statement is the correct one).

Note: I'm sorry I can't test this on 1.11, as I'm using MultipleChoiceField in forms and haven't figured out a way to replace it yet. The release notes only say those (undocumented classes) were removed, but nothing on what to use instead. I can confirm though that 1.9 exhibits the same behaviour.

[Tim Graham]

The date=timezone.now().date() code looks suspicious. Since the field has auto_now=True, it uses ​datetime.date.today() as the creation value, regardless of you provide. I'd suggest replacing date=timezone.now().date() with date=datetime.date.today() and checking if the issue still happens. Please reopen the ticket if you identify that Django is really at fault.

[Jure Erznožnik]

Removing auto_now=True solved the problem.
I still don't see how that IS the solution: I WAS using now() for date in searc / create. auto_now should make o difference.
Thanks for the suggestion.

[Tim Graham]

The get() in get_or_create() used timezone.now().date() while the create() used datetime.date.today()`. The values might be different.