Python Count number of records within a given date range

Question

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

Assign each date range a key in a dictionary, +1 to the value each time you find an item within that range? — cdarke
– cdarke, Commented Jul 27, 2012 at 9:31
@Prakash: sorry, I don't know (looks like you have the answer anyway) — cdarke
– cdarke, Commented Jul 27, 2012 at 9:47

eumiro · Accepted Answer · 2012-07-27 09:35:28Z

1

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)

edited Jul 27, 2012 at 9:35

answered Jul 27, 2012 at 9:29

eumiro

214k36 gold badges307 silver badges264 bronze badges

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

7 Comments

Programmer Over a year ago

Thanks a lot but one question - if in case for a date there are no records I should get something like : 2012-07-04 0 also. Thanks its a great help

Martijn Pieters Over a year ago

Perfect :-) You already had my upvote, I saw no sense in scooping you on that small issue alone..

Martijn Pieters Over a year ago

@Prakash: That'll complicate things enormously. Use python logic for that part (parse the whole thing into a dictionary, for example, then handle missing dates via get(date, 0)).

eumiro Over a year ago

@Prakash - you will need some datetime logic there (which knows there is 2012-02-29 but no 2012-02-30, and so on). You can build it in Python with dateutil.rrule, which is simpler than doing the same thing in MySQL.

Martijn Pieters Over a year ago

@eumiro: A simple date += timedelta(days=1) loop should suffice should it not?

|

Collectives™ on Stack Overflow

Python Count number of records within a given date range

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related