Timestamp fields in django

Question

I have a MySQL database, right now I'm generating all of the datetime fields as models.DateTimeField. Is there a way to get a timestamp instead? I want to be able to autoupdate on create and update etc.

The documentation on django doesn't have this?

Solution 1

There was actually a very good and informative article on this. Here: http://ianrolfe.livejournal.com/36017.html

The solution on the page is slightly deprecated, so I did the following:

from django.db import models
from datetime import datetime
from time import strftime
class UnixTimestampField(models.DateTimeField):
    """UnixTimestampField: creates a DateTimeField that is represented on the
    database as a TIMESTAMP field rather than the usual DATETIME field.
    """
    def __init__(self, null=False, blank=False, **kwargs):
        super(UnixTimestampField, self).__init__(**kwargs)
        # default for TIMESTAMP is NOT NULL unlike most fields, so we have to
        # cheat a little:
        self.blank, self.isnull = blank, null
        self.null = True # To prevent the framework from shoving in "not null".
    def db_type(self, connection):
        typ=['TIMESTAMP']
        # See above!
        if self.isnull:
            typ += ['NULL']
        if self.auto_created:
            typ += ['default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP']
        return ' '.join(typ)
    def to_python(self, value):
        if isinstance(value, int):
            return datetime.fromtimestamp(value)
        else:
            return models.DateTimeField.to_python(self, value)
    def get_db_prep_value(self, value, connection, prepared=False):
        if value==None:
            return None
        # Use '%Y%m%d%H%M%S' for MySQL < 4.1
        return strftime('%Y-%m-%d %H:%M:%S',value.timetuple())

To use it, all you have to do is: timestamp = UnixTimestampField(auto_created=True)

In MySQL, the column should appear as: 'timestamp' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Only drawback with this is that it only works on MySQL databases. But you can easily modify it for others.

Solution 2

To automatically update on insert and update use this:

created = DateTimeField(auto_now_add=True, editable=False, null=False, blank=False)
last_modified = DateTimeField(auto_now=True, editable=False, null=False, blank=False)

The DateTimeField should store UTC (check your DB settings, I know from Postgres that there it is the case). You can use l10n in the templates and format via:

{{ object.created|date:'SHORT_DATETIME_FORMAT' }}

Seconds since Unix Epoch:

{{ object.created|date:'U' }}

See https://docs.djangoproject.com/en/1.10/ref/templates/builtins/#date

Solution 3

The pip package django-unixdatetimefield provides a UnixDateTimeField field that you can use for this out of the box (https://pypi.python.org/pypi/django-unixdatetimefield/).

Example model:

from django_unixdatetimefield import UnixDateTimeField
class MyModel(models.Model):
    created_at = UnixDateTimeField()

Python ORM query:

>>> m = MyModel()
>>> m.created_at = datetime.datetime(2015, 2, 21, 19, 38, 32, 209148)
>>> m.save()

Database:

sqlite> select created_at from mymodel;
1426967129

Here's the source code if interested - https://github.com/Niklas9/django-unixdatetimefield.

Disclaimer: I'm the author of this pip package.

Comments

[coredumperror]

The get_db_prep_value function is out of date, as it only applies to TIMESTAMP columns on MySQL < 4.1. For modern versions of MySQL use '%Y-%m-%d %H:%M:%S' instead of '%Y%m%d%H%M%S'.

[KVISH]

does it have options for NULL etc?

[Niklas9]

it does, it subclasses the standard DateTimeField in Django, which in turn supports NULL etc. See code line 8 at - github.com/Niklas9/django-unixdatetimefield/blob/master/…

[Ytsen de Boer]

For django 1.8 you should implement from_db_value to convert the database value to your object's attribute.

[Bruno]

I am trying to insert data from Django Rest Framework, and when I send the payload: { "timeStamp": 1581950810, } I get this error: { "timeStamp": [ "Datetime has wrong format. Use one of these formats instead: YYYY-MM-DDThh:mm[:ss[.uuuuuu]][+HH:MM|-HH:MM|Z]." ] } Is it possible to accept timestamp on the field?

[Niklas9]

You're using it the wrong way. If you need to send unix timestamps to your API, you should use Django Rest Framework to handle it, no need to use this library.

[cambunctious]

That just uses DateTimeField to add created and modified fields.