Postgresql SQL query: getting a count of everyday within a period

Question

Recently I wanna make a sql query on PostgreSQL that can do a counting of a table records that of everyday (or maybe every hour) of a certain period (a month or a day etc).

A loop through every day of a period (month), in other words.

How can I do this?

I wish the record could be something similar to this:

--------------------
count | day | month |
1     | 1   |  4    |
1     | 2   |  4    |
1     | 3   |  4    |
1     | 4   |  4    |
1     | 5   |  4    |

Update:

I have a table called visitors:

first_seen:date_time; last_seen: date time; id: integer

I wanna calculate total visitor of every day of a month. But since a visitor can be shown up more than multiple day a time, i.e stayed for 3 days. I found a simple count(id) wont do the trick.

Update 2:

This is what i did so far, but apparently wrong.

SELECT count("visitors"."id"), 

EXTRACT(hour from visitors.last_seen) AS hour, 
EXTRACT(day from "visitors"."last_seen") AS date, 
EXTRACT(month from "visitors"."last_seen") AS month 

FROM "visitors"
WHERE (("visitors"."first_seen" between '2014-04-09 15:30:07.423401' and '2014-04-10 15:30:07.423476') 
or ("visitors"."last_seen" between '2014-04-09 15:30:07.423401' and  '2014-04-10 15:30:07.423476') 
or ("visitors"."first_seen" < '2014-04-09 15:30:07.423401' and "visotors".last_seen > '2014-04-10 15:30:07.423476')) 

GROUP BY month,date,hour ORDER BY month asc, date asc , hour asc

Sorry for the lack of informations.

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. Please provide the query that you've attempted and sample data. — Kermit
– Kermit, Commented Apr 11, 2014 at 14:09

digoal.zhou · Accepted Answer · 2014-04-11 14:42:40Z

2

You can use generate_series fill the gap data when vistor spead the statistic perids. and then group it. for exp :

select count(id),
EXTRACT(hour from visitors.last_seen) AS hour, 
EXTRACT(day from "visitors"."last_seen") AS date, 
EXTRACT(month from "visitors"."last_seen") AS month 
from
(select id,generate_series(first_seen,last_seen,'1 hour'::interval) from tbl) t
where ...
GROUP BY month,date,hour 

ORDER BY month asc, date asc , hour asc

answered Apr 11, 2014 at 14:42

digoal.zhou

4342 silver badges3 bronze badges

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

Comments

Gustavo De Mari · Accepted Answer · 2014-04-11 15:04:18Z

0

Use the DATE_TRUNC function.

SELECT count(id),
       date_trunc('day', last_seen) AS DATE
 FROM visitors
  GROUP BY date_trunc('day', last_seen);

answered Apr 11, 2014 at 15:04

Gustavo De Mari

11 silver badge2 bronze badges

1 Comment

nnsense Over a year ago

This is counting all IDs by day regardless the user logged in multiple times.

Collectives™ on Stack Overflow

Postgresql SQL query: getting a count of everyday within a period

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related