Retrieve UNIX TIMESTAMP in django api call

Question

I have the following query:

users = Analytics.objects.values('date', 'users').order_by('date')
[(datetime.date(2012, 8, 20), 156L), (datetime.date(2012, 8, 21), 153L),...]

How would I get the unix timestamp of the datetime here? The equivalent of doing:

select concat(UNIX_TIMESTAMP(date), '000') as date, users from analytics_analytics
1345446000000   156
1345532400000   153
1345618800000   153

Note that I do not want to do formatting in python as there are a lot of the above calls that need to be done.

gcbirzan · Accepted Answer · 2012-08-22 08:15:43Z

1

The best way to do this, if really want the DB to do the conversion (and this can be applicable for other things, in general when you want to return information that's not readily available in a column from the DB), you can use the QuerySet.extra() function https://docs.djangoproject.com/en/dev/ref/models/querysets/#extra like this:

Analytics.objects.extra(select={'timestamp': "CONCAT(UNIX_TIMESTAMP(date), '000')"}).order_by('date').values('timestamp', 'users')

Downside of this is that it's no longer DB agnostic, so when you change RDBMS, you have to change the code. Upside is you can use values with it, unlike a property on the model.

answered Aug 22, 2012 at 8:15

gcbirzan

1,53411 silver badges17 bronze badges

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

Comments

Martijn Pieters · Accepted Answer · 2012-08-22 08:10:05Z

Python datetime.date objects don't have a time component, so the timestamp you want needs a little qualification. If midnight suffices, you can use the .timetuple() method together with the time.mktime() function to create a timestamp:

>>> import datetime, time
>>> adate = datetime.date(2012, 8, 20)
>>> print time.mktime(adate.timetuple())
1345413600.0

If you need a specific time in the day, use the datetime.datetime.combine() class method to construct a datetime, then use the same trick to make it a timestamp:

>>> adatetime = datetime.datetime.combine(adate, datetime.time(12, 0))
>>> print time.mktime(adatetime.timetuple())
1345456800.0

Use either as a property on your Django model:

class Analytics(models.Model):
    @property
    def timestamp(self):
        return time.mktime(self.date.timetuple())

schacki · Accepted Answer · 2012-08-22 08:10:58Z

0

I am not familiar with UNIX_TIMESTAMP, but with regard to Django: why not create a calculated field as proprty at your Django Model

class Analytics(...):
    ...
    @property
    def unix_timestamp(self):
        return time.mktime(self.date.timetuple())

answered Aug 22, 2012 at 8:10

schacki

9,5335 gold badges31 silver badges32 bronze badges

Collectives™ on Stack Overflow

Retrieve UNIX TIMESTAMP in django api call

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related