CODEWITHSUNDEEP
phpmysqlpagination
ian
ian

Reputation: 12335

how to count the number of dates in a table

I have a small custom blog type site and I keep track of my posts with a datetime field in the DB that hold a current_timestamp when entered.

I am working on a pagination setup and want to figure out how many unique days there are in my DB.

For example in the below screen shot there are 19 entries but only entered on 5 different days.

date times screenshot

I want to be able to find the number of unique days in the DB. In the above shot 5.

This way I can paginate my posts by groups of days, say 7 but still show all the posts for all those days regardless of if one day has more posts or one day has none.

EX for page 1 showing the first 7 days of posts:

Jan 1st +post +post +post

Jan 3 +post

Jan 4 +post +post

Jan 5 +post

Jan 6 +post +post

Jan 7 +post

Jan 8 +post +post

How is the best way to get this data or paginate in this way? Thanks

Upvotes: 0

Views: 91

Answers (3)

user319198
user319198

Reputation:

Try below:

select count(*),date from tablename group by left(date,10)

above query will give count of unique days.

Upvotes: 1

mkk
mkk

Reputation: 7693

I guess what you are looking for is

SELECT count(*) from table group by DATE(date)

the trick is to map datetime into date via DATE() function

UPDATE: sorry I missinterpreted your question. This will return the number of posts in each day.

To get number list of unique days you could do

SELECT DISTINCT DATE(datetime) from table

To get number of unique days you could either check the count of it in your back-end language or make a subquery, like:

select count(*) from (select distinct date(datetime) from table) as res

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

This will give you the amount of posts per day:

select from_days(to_days(date)) day, count(*) from t
group by day

You can then apply a limit to the amount of records (days) to paginate them per 7 days:

select from_days(to_days(date)) day, count(*) from t
group by day
limit 7

Now, if you want to get the actual posts (I think that is what you want), you can do this:

select date, file from t t1 join (
  select to_days(date) day from t
  group by day
  order by day desc
  limit 7
) t2
on to_days(t1.date) = t2.day

This will give you all posts for the 7 most current days. Is that what you where looking for?

Edit:

I may be misinterpreting or did not ask my question well but I want the the count of all total unique days not posts. - ian

LOL: That's easier :)

select count(distinct to_days(date)) from t

Upvotes: 0

Related Questions