CODEWITHSUNDEEP
pythonmysql
Programmer
Programmer

Reputation: 8717

Python Count number of records within a given date range

We have a backend table that stores details of transaction including seconds since epoch. I am creating a UI where I collect from-to dates to display counts of transaction occurred in-between the dates.

Assuming that the date range is from 07/01/2012 - 07/30/2012, I am unable to establish a logic that will increment a counter for records that happened within the time period. I should hit the DB only once as hitting for each day will give poor performance.

I am stuck at a logic:

Convert 07/01/2012 & 07/30/2012 to seconds since epoch.
Get the records for start date - end date [as converted to seconds since epoch] 
   For each record get the month / date 
     -- now how will we add counters for each date in between 07/01/2012 - 07/30/2012

Upvotes: 0

Views: 1103

Answers (1)

eumiro
eumiro

Reputation: 213015

MySQL has the function FROM_UNIXTIME which will convert your seconds since epoch into datetime and you can then extract the DATE part of it (YYYY-MM-DD format) and group according to it.

SELECT DATE(FROM_UNIXTIME(timestamp_column)), COUNT(*)
FROM table_name
GROUP BY DATE(FROM_UNIXTIME(timestamp_column))

This will return something like

2012-07-01  2
2012-07-03  4
…

(no entries for days without transactions)

Upvotes: 1

Related Questions