87

I'm trying to write a Django query for widgets that are more than 5 hours old and I'm a bit lost. The widget model has a DateTimeField that is populated with the creation time of the widget.

Kevin Brown-Silva
  • 40,873
  • 40
  • 203
  • 237
user523513
  • 957
  • 1
  • 7
  • 8

4 Answers4

189

If Widget is the name of your model, and it has a DateTimeField attribute named created, the query would be:

from datetime import datetime, timedelta

time_threshold = datetime.now() - timedelta(hours=5)
results = Widget.objects.filter(created__lt=time_threshold)

Note that created__lt means "created is less than".

David Robinson
  • 77,383
  • 16
  • 167
  • 187
  • 1
    How we would achieve the same thing if `hours` came from the table column and was not a hardcoded value? – adkl Jan 05 '18 at 22:04
  • is this going to filter records created > 5 hours (created is more than five hours?) – Grant Gubatan Aug 01 '18 at 16:10
  • @GrantGubatan Not quite: the `__lt` stands for "less than", so it's filtering for records whose time stamp is less then (is before) five hours *ago*. – David Robinson Aug 02 '18 at 13:47
  • 2
    If your DateTimeField uses `auto_now`, then its value will be set by the database server. The `datetime.now() - timedelta(hours=5)` will be calculated on the database client, which might not correspond to the database server's time. How can I make the age of the record be calculated completely on the database server? – JoeMjr2 Mar 12 '19 at 20:47
  • 4
    If tz support is active, use `from django.utils import timezone` & `timezone.now()` instead of `datetime.now()` – Dustin Michels Mar 21 '19 at 17:26
20
now = datetime.datetime.now()
earlier = now - datetime.timedelta(hours=5)
MyModel.objects.filter(my_date_field__range=(earlier,now))

That should do the trick.

Josh Smeaton
  • 47,939
  • 24
  • 129
  • 164
14

if settings.USE_TZ = True and settings.TIME_ZONE is setting

from django.utils import timezone
five_h_ago = timezone.now()-timezone.timedelta(hours=5)
example.object.filter(datetimefield__lt=five_h_ago)
tao.song
  • 139
  • 1
  • 3
1

The simplest approach has already been covered by other answers: just filter the records where the date is earlier than five hours ago. Here's a full example that finds records created at least five seconds ago:

# Tested with Django 1.11.15 and Python 3.6.
import logging
import sys
from datetime import datetime, timedelta
from time import sleep

import django
from django.apps import apps
from django.apps.config import AppConfig
from django.conf import settings
from django.db import connections, models, DEFAULT_DB_ALIAS
from django.db.models.base import ModelBase

NAME = 'udjango'
DB_FILE = NAME + '.db'


def main():
    setup()
    logger = logging.getLogger(__name__)

    class Widget(models.Model):
        name = models.CharField(max_length=200)
        date_created = models.DateTimeField(auto_now_add=True)

        def __str__(self):
            return self.name

    syncdb(Widget)

    Widget.objects.create(name='spline')
    sleep(1)
    Widget.objects.create(name='reticulator')
    sleep(1)
    Widget.objects.create(name='tardis')
    sleep(5)
    Widget.objects.create(name='sonic screwdriver')
    sleep(1)

    cutoff_time = datetime.now() - timedelta(seconds=5)
    for widget in Widget.objects.filter(date_created__lt=cutoff_time):
        logger.info(widget.name)


def setup():
    with open(DB_FILE, 'w'):
        pass  # wipe the database
    settings.configure(
        DEBUG=True,
        DATABASES={
            DEFAULT_DB_ALIAS: {
                'ENGINE': 'django.db.backends.sqlite3',
                'NAME': DB_FILE}},
        LOGGING={'version': 1,
                 'disable_existing_loggers': False,
                 'formatters': {
                    'debug': {
                        'format': '[%(levelname)s]'
                                  '%(name)s.%(funcName)s(): %(message)s',
                        'datefmt': '%Y-%m-%d %H:%M:%S'}},
                 'handlers': {
                    'console': {
                        'level': 'DEBUG',
                        'class': 'logging.StreamHandler',
                        'formatter': 'debug'}},
                 'root': {
                    'handlers': ['console'],
                    'level': 'INFO'},
                 'loggers': {
                    "django.db": {"level": "INFO"}}})
    app_config = AppConfig(NAME, sys.modules['__main__'])
    apps.populate([app_config])
    django.setup()
    original_new_func = ModelBase.__new__

    @staticmethod
    def patched_new(cls, name, bases, attrs):
        if 'Meta' not in attrs:
            class Meta:
                app_label = NAME
            attrs['Meta'] = Meta
        return original_new_func(cls, name, bases, attrs)
    ModelBase.__new__ = patched_new


def syncdb(model):
    """ Standard syncdb expects models to be in reliable locations.

    Based on https://github.com/django/django/blob/1.9.3
    /django/core/management/commands/migrate.py#L285
    """
    connection = connections[DEFAULT_DB_ALIAS]
    with connection.schema_editor() as editor:
        editor.create_model(model)


main()

That shows me all but the last widget:

[INFO]__main__.main(): spline
[INFO]__main__.main(): reticulator
[INFO]__main__.main(): tardis

That works fine, unless you have enabled time zone support. In the previous example, I do that by changing settings.configure(... to look like this:

settings.configure(
    USE_TZ=True,
    ...

When I do that, I get a message like this:

RuntimeWarning: DateTimeField Widget.date_created received a naive datetime (2019-01-07 16:39:04.563563) while time zone support is active.

To get a time-zone-aware date, use timezone.now() function instead of datetime.now():

# Tested with Django 1.11.15 and Python 3.6.
import logging
import sys
from datetime import timedelta
from time import sleep

import django
from django.apps import apps
from django.apps.config import AppConfig
from django.conf import settings
from django.db import connections, models, DEFAULT_DB_ALIAS
from django.db.models.base import ModelBase
from django.utils import timezone

NAME = 'udjango'
DB_FILE = NAME + '.db'


def main():
    setup()
    logger = logging.getLogger(__name__)

    class Widget(models.Model):
        name = models.CharField(max_length=200)
        date_created = models.DateTimeField(auto_now_add=True)

        def __str__(self):
            return self.name

    syncdb(Widget)

    Widget.objects.create(name='spline')
    sleep(1)
    Widget.objects.create(name='reticulator')
    sleep(1)
    Widget.objects.create(name='tardis')
    sleep(5)
    Widget.objects.create(name='sonic screwdriver')
    sleep(1)

    cutoff_time = timezone.now() - timedelta(seconds=5)
    for widget in Widget.objects.filter(date_created__lt=cutoff_time):
        logger.info(widget.name)


def setup():
    with open(DB_FILE, 'w'):
        pass  # wipe the database
    settings.configure(
        USE_TZ=True,
        DEBUG=True,
        DATABASES={
            DEFAULT_DB_ALIAS: {
                'ENGINE': 'django.db.backends.sqlite3',
                'NAME': DB_FILE}},
        LOGGING={'version': 1,
                 'disable_existing_loggers': False,
                 'formatters': {
                    'debug': {
                        'format': '[%(levelname)s]'
                                  '%(name)s.%(funcName)s(): %(message)s',
                        'datefmt': '%Y-%m-%d %H:%M:%S'}},
                 'handlers': {
                    'console': {
                        'level': 'DEBUG',
                        'class': 'logging.StreamHandler',
                        'formatter': 'debug'}},
                 'root': {
                    'handlers': ['console'],
                    'level': 'INFO'},
                 'loggers': {
                    "django.db": {"level": "INFO"}}})
    app_config = AppConfig(NAME, sys.modules['__main__'])
    apps.populate([app_config])
    django.setup()
    original_new_func = ModelBase.__new__

    @staticmethod
    def patched_new(cls, name, bases, attrs):
        if 'Meta' not in attrs:
            class Meta:
                app_label = NAME
            attrs['Meta'] = Meta
        return original_new_func(cls, name, bases, attrs)
    ModelBase.__new__ = patched_new


def syncdb(model):
    """ Standard syncdb expects models to be in reliable locations.

    Based on https://github.com/django/django/blob/1.9.3
    /django/core/management/commands/migrate.py#L285
    """
    connection = connections[DEFAULT_DB_ALIAS]
    with connection.schema_editor() as editor:
        editor.create_model(model)


main()

Occasionally, I have had problems where the database's clock gets out of synch with the web server's clock. To avoid that kind of problem, you can use the Now() function to get the current time in the database.

# Tested with Django 1.11.15 and Python 3.6.
import logging
import sys
from datetime import timedelta
from time import sleep

import django
from django.apps import apps
from django.apps.config import AppConfig
from django.conf import settings
from django.db import connections, models, DEFAULT_DB_ALIAS
from django.db.models.base import ModelBase
from django.db.models.functions import Now
from django.utils import timezone

NAME = 'udjango'
DB_FILE = NAME + '.db'


def main():
    setup()
    logger = logging.getLogger(__name__)

    class Widget(models.Model):
        name = models.CharField(max_length=200)
        date_created = models.DateTimeField()

        def __str__(self):
            return self.name

    syncdb(Widget)

    Widget.objects.create(name='spline', date_created=Now())
    sleep(1)
    Widget.objects.create(name='reticulator', date_created=Now())
    sleep(1)
    Widget.objects.create(name='tardis', date_created=Now())
    sleep(5)
    Widget.objects.create(name='sonic screwdriver', date_created=Now())
    sleep(1)

    cutoff_time = Now() - timedelta(seconds=5)
    for widget in Widget.objects.filter(date_created__lt=cutoff_time):
        logger.info(widget.name)


def setup():
    with open(DB_FILE, 'w'):
        pass  # wipe the database
    settings.configure(
        USE_TZ=True,
        DEBUG=True,
        DATABASES={
            DEFAULT_DB_ALIAS: {
                'ENGINE': 'django.db.backends.sqlite3',
                'NAME': DB_FILE}},
        LOGGING={'version': 1,
                 'disable_existing_loggers': False,
                 'formatters': {
                    'debug': {
                        'format': '[%(levelname)s]'
                                  '%(name)s.%(funcName)s(): %(message)s',
                        'datefmt': '%Y-%m-%d %H:%M:%S'}},
                 'handlers': {
                    'console': {
                        'level': 'DEBUG',
                        'class': 'logging.StreamHandler',
                        'formatter': 'debug'}},
                 'root': {
                    'handlers': ['console'],
                    'level': 'INFO'},
                 'loggers': {
                    "django.db": {"level": "INFO"}}})
    app_config = AppConfig(NAME, sys.modules['__main__'])
    apps.populate([app_config])
    django.setup()
    original_new_func = ModelBase.__new__

    @staticmethod
    def patched_new(cls, name, bases, attrs):
        if 'Meta' not in attrs:
            class Meta:
                app_label = NAME
            attrs['Meta'] = Meta
        return original_new_func(cls, name, bases, attrs)
    ModelBase.__new__ = patched_new


def syncdb(model):
    """ Standard syncdb expects models to be in reliable locations.

    Based on https://github.com/django/django/blob/1.9.3
    /django/core/management/commands/migrate.py#L285
    """
    connection = connections[DEFAULT_DB_ALIAS]
    with connection.schema_editor() as editor:
        editor.create_model(model)


main()

I haven't seen that problem in recent years, so it's probably not worth the hassle in most cases.

Don Kirkby
  • 53,582
  • 27
  • 205
  • 286