CODEWITHSUNDEEP
pythonmysqldjango
David542
David542

Reputation: 110562

Retrieve UNIX TIMESTAMP in django api call

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.

Upvotes: 0

Views: 917

Answers (3)

gcbirzan
gcbirzan

Reputation: 1524

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.

Upvotes: 1

schacki
schacki

Reputation: 9533

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())

Upvotes: 0

Martijn Pieters
Martijn Pieters

Reputation: 1124968

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())

Upvotes: 1

Related Questions