django set DateTimeField to database server's current time

Question

How do I do the equivalent of this SQL in django?

UPDATE table SET timestamp=NOW() WHERE ...

Particularly I want to set the datetime field using server's builtin function to get the system time from the server that the database was running on and not the time on the client machine.

I know you can execute the raw sql directly but I'm looking for a more portable solution since databases have different functions for getting the current datetime.

Edit: few people mentioned auto_now param. This updates the datetime on every modification while I want to update datetime only on certain occasions.

Fairspoken

Answer 1

As j0ker said, if you want automatic update of the timestamp, use the auto_now option. E.g. date_modified = models.DateTimeField(auto_now=True).

If you want to set the field to now only when the object is first created you should use:

date_modified = models.DateTimeField(auto_now_add=True)

Or if you want to do it manually, isn't it a simple assignment with python datetime.now()?

from datetime import datetime

obj.date_modified = datetime.now()

Jealousy

Auto_now updates datetime field on every update which I don't want. And python's datetime.now() will get you current datetime on client side which is not guaranteed to be even close to the datetime on the server. – Fairspoken

How will datetime.now() use the client's datetime? All your Python-Code in the views is executed on the server, so of course it uses the server's datetime. – Murton

By server I mean the machine that host the database, and client is the machine that runs python/django and access the data from the database. The usecase here is that when multiple client machines are accessing the database and updating the timestamps, if the clients' system time are not synced up (which is sometime not possible due to lack of permission) it will be hard to figure out the order which the entries are updated - this is why it's better to use system time on the server so the timestamps are from the same clock. – Fairspoken

Now I understand. You should add that to your original question since I don't think it comes clear, that you are referring to the database server. – Murton

Just to clarify, this is NOT the correct answer to this question. Please see @tvorog's answer further down. – Denys

Answer 2

The accepted answer is outdated. Here's the current and most simple way of doing so:

>>> from django.utils import timezone
>>> timezone.now()
datetime.datetime(2018, 12, 3, 14, 57, 11, 703055, tzinfo=<UTC>)

Diplopia

Pretty sure this uses the clock on the machine django is running on, not the database's clock which the OP wants. – Sandstorm

Answer 3

You can use database function Now starting Django 1.9:

from django.db.models.functions import Now
Model.objects.filter(...).update(timestamp=Now())

Chitterlings

As per the docs, this returns the DB server's time and appears to be the most consistant with auto_now and others. – Zirkle

This is the correct answer. Please note that Now() translates to statement_timestamp() on PostgreSQL (and maybe others), which is equivalent to now() in most normal use-cases (check the docs for more details). – Denys

Answer 4

Here is how I solved this issue. Hope it saves someone time:

from django.db import models

class DBNow(object):
    def __str__(self):
        return 'DATABASE NOW()'
    def as_sql(self, qn, val):
        return 'NOW()', {}
    @classmethod
    def patch(cls, field):
        orig_prep_db = field.get_db_prep_value
        orig_prep_lookup = field.get_prep_lookup
        orig_db_prep_lookup = field.get_db_prep_lookup

        def prep_db_value(self, value, connection, prepared=False):
            return value if isinstance(value, cls) else orig_prep_db(self, value, connection, prepared)

        def prep_lookup(self, lookup_type, value):
            return value if isinstance(value, cls) else orig_prep_lookup(self, lookup_type, value)

        def prep_db_lookup(self, lookup_type, value, connection, prepared=True):
            return value if isinstance(value, cls) else orig_db_prep_lookup(self, lookup_type, value, connection=connection, prepared=True)

        field.get_db_prep_value = prep_db_value
        field.get_prep_lookup = prep_lookup
        field.get_db_prep_lookup = prep_db_lookup

# DBNow Activator
DBNow.patch(models.DateTimeField)

And then just using the DBNow() as a value where updating and filtering is needed:

books = Book.objects.filter(created_on__gt=DBNow())

    or:

book.created_on = DBNow()
book.save()

Gabriel

May I know the version of your Django? I am using the sample with my project on Django 1.6 but it's not working as Django will construct the SQL with the str's return value instead of as_sql's one. – Hyacinthia

Answer 5

You can use something like this to create a custom value to represent the use of the current time on the database:

class DatabaseDependentValue(object):
    def setEngine(self, engine):
        self.engine = engine

    @staticmethod
    def Converter(value, *args, **kwargs):
        return str(value)

class DatabaseNow(DatabaseDependentValue):
    def __str__(self):
        if self.engine == 'django.db.backends.mysql':
            return 'NOW()'
        elif self.engine == 'django.db.backends.postgresql':
            return 'current_timestamp'
        else:
            raise Exception('Unimplemented for engine ' + self.engine)

django_conversions.update({DatabaseNow: DatabaseDependentValue.Converter})

def databaseDependentPatch(cls):
    originalGetDbPrepValue = cls.get_db_prep_value
    def patchedGetDbPrepValue(self, value, connection, prepared=False):
        if isinstance(value, DatabaseDependentValue):
            value.setEngine(connection.settings_dict['ENGINE'])
            return value
        return originalGetDbPrepValue(self, value, connection, prepared)
    cls.get_db_prep_value = patchedGetDbPrepValue

And then to be able to use DatabaseNow on a DateTimeField:

databaseDependentPatch(models.DateTimeField)

Which then in turn finally allows you do a nice and clean:

class Operation(models.Model):
    dateTimeCompleted = models.DateTimeField(null=True)
    # ...

operation = # Some previous operation
operation.dateTimeCompleted = DatabaseNow()
operation.save()

Virge

It turns out that for me django_conversions.update is a must to make it work, which is missing in some other answers. I am using Django 1.6. – Hyacinthia

Answer 6

My tweaked code works with sqlite, mysql and postgresql and is a bit cleaner than the proposed solutions.

class DBCurrentTimestamp:
    def __str__(self):
        return 'DATABASE CURRENT_TIMESTAMP()'

    def as_sql(self, qn, connection):
        return 'CURRENT_TIMESTAMP', {}

    @classmethod
    def patch(cls, *args):
        def create_tweaked_get_db_prep_value(orig_get_db_prep_value):
            def get_db_prep_value(self, value, connection, prepared=False):
                return value if isinstance(value, cls) else orig_get_db_prep_value(self, value, connection, prepared)

            return get_db_prep_value

        for field_class in args:
            field_class.get_db_prep_value = create_tweaked_get_db_prep_value(field_class.get_db_prep_value)

I activate it @ the end of my models.py file like this:

DBCurrentTimestamp.patch(models.DateField, models.TimeField, models.DateTimeField)

and use it like this:

self.last_pageview = DBCurrentTimestamp()

Philanthropy

Answer 7

I've created a Python Django plugin module which allows you to control the use of CURRENT_TIMESTAMP on DateTimeField objects, both in specific cases (see usage below) as well as automatically for auto_now and auto_now_add columns.

django-pg-current-timestamp

GitHub: https://github.com/jaytaylor/django-pg-current-timestamp

PyPi: https://pypi.python.org/pypi/django-pg-current-timestamp

Example usage:

from django_pg_current_timestamp import CurrentTimestamp

mm = MyModel.objects.get(id=1)
mm.last_seen_date = CurrentTimestamp()
mm.save()
## Resulting SQL:
##     UPDATE "my_model" SET "last_seen_date" = CURRENT_TIMESTAMP;

print MyModel.objects.filter(last_seen_date__lt=CURRENT_TIME).count()

MyModel.objects.filter(id__in=[1, 2, 3]).update(last_seen_date=CURRENT_TIME)

Wiesbaden

This is outdated. – Diplopia

Answer 8

If you want the datetime from a foreign server (i.e., not the one hosting the Django application), you're going to have to peg it manually for a datatime to use. You could use a SQL command like select now(); or something over SSH, like ssh user@host "date +%s".

Maffick

This is more complicated than executing a raw update sql directly (which was mentioned in the question) – Fairspoken

I was under the impression that you wanted the sql server's time on a form being displayed by Django - do you simply want the sql server to set it's own time during insert/update? – Maffick

Answer 9

Maybe you should take a look into the documentation:
Modelfields: DateField

The option 'auto_now' could be just what you are searching for. You can also use it with the DateTimeField. It updates the DateTime each time you're saving the model. So with that option set for your DateTimeField it should be sufficent to retrieve a data-record and save it again to set the time right.

Murton

Then I would recomment jesse's datetime.now()-solution. – Murton

Answer 10

When creating the table, the field you want to make date now in field after add data add this code to field

class MyModel(models.Model):
  created_at = models.DateTimeField(auto_now_add=True)
  updated_at = models.DateTimeField(auto_now=True)

Osuna