CODEWITHSUNDEEP
mysqlcodeigniter
Jorg Ancrath
Jorg Ancrath

Reputation: 1447

Count all database entries separated by days

I have a table with the following format:

offer_id     consumer_id     date
1            1               1282454200
1            1               1282453200
2            2               1282453240
1            3               1282455200
2            1               1282453210

"date" is in unix format.

I need to count all of the daily entries, so if I have 10 entries from yesterday and 8 entries from today, I should get:

2013-06-23    10
2013-06-24    8

This is part of my work on trying to optimize code, so far I have been doing this via PHP code, but you can imagine what happens with a growing database :). This is my php (codeigniter) attempt that I'm trying (unsuccessfully) to translate into mysql:

foreach ($offers as $item) {
            $date = $item->date;
            $day_date = date("Y-m-d", $date);
            $day_start = strtotime(date("Y-m-d 00:00:00", $date));
            $day_end = strtotime(date("Y-m-d 23:59:59", $date));
            if (!in_array($day_date, $day_array)) {
                $day_array[] = $day_date;

                $this->db->where("date >=", $day_start);
                $this->db->where("date <=", $day_end);
                $this->db->from("offers_consumers_history");
                $total_points = $this->db->count_all_results();

                $db_date = array($day_date, $total_points);
                $data[] = $db_date;
            }
        }

I basically grabbed all of the entries in a previous query and went through every date, if the date isn't in my final array, I had to it by counting all results from 00:00:00 to 23:59:59.

Looking for help in building equivalent SQL code.

Upvotes: 1

Views: 88

Answers (3)

Blanca Hdez
Blanca Hdez

Reputation: 3563

If I have right understood your question, group by is what you need

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You could use this SQL query:

SELECT   DATE(FROM_UNIXTIME(date)), COUNT(*)
FROM     offers_consumers_history
GROUP BY DATE(FROM_UNIXTIME(date))

Please see fiddle here.

Upvotes: 2

GautamD31
GautamD31

Reputation: 28763

Try like

SELECT count(*) as cnt , date FROM `my_table` GROUP BY date

Then you can change them as your required format.It is simple and same that to change the dates into FROM_UNIXTIME and then counting

Upvotes: 0

Related Questions