How to implement posting limit of a user?

Question

A user can post 4 times at most within a day.

The difficulty is how to get the start of a day in database?I'm using MySQL

Answer 1

This will get records based on today:

SELECT * FROM whatever WHERE date(datefield) = CURdate();

Answer 2

SELECT datetime FROM posts ORDER BY date LIMIT 3,1

if now - datetime >= 86400:            // pick one
if datetime.datepartonly < today:
    allow posting
else:
    deny posting

---

If you are sure that the time of your MySQL server is set correctly:

SELECT COUNT(*) FROM posts WHERE DATE_SUB(NOW(), 1 day) <= datetime; // OR
SELECT COUNT(*) FROM posts WHERE CURRENT_DATE() <= datetime;
if count < 4:
    allow
else:
    deny

Answer 3

It would be better if you store his TimeZone. People in different parts of the world have different time, so start of the day would be different for each one of them.

You can get the current date and current time. Check how many posts have been made from the start of the day 00:00 on this particular day. If the number equals to 4, then tell the user that he has reached the maximum limit.

Using NOW() you can use to get the current date and time

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2009-10-22 10:49:15 |
+---------------------+
1 row in set (0.00 sec)

You must be knowing at what timezone your server is, then make the time UTC and add/subtract the time to get the Local time of the user.

You would probably like to have a look at the MySQL Data and time Reference

• DATE() - get the current date
• CURTIME() - Get the current time
• CONVERT_TZ - converting from one time zone to another

How to get the start of the day in Database

It should not be a problem, as I said, get the current time from DB, adjust your time difference to make the time UTC. then adjust the timezone of the user to make the time as per his timezone.

e.g. 3:30(server time) - 1:00(server timezone) + 5:30(User's timezone) = 8:00 (user's time)

So, the start of the day for this user is 00:00 and it's 8 hrs elapsed for this user.

N.B: Take care of the time difference. e.g. 2:30 - 3:30 = -1:00 which means 23:00 on the previous day. Take this also in consideration.

Answer 4

Rather than focusing on the day, why not limit to 4 posts on a rolling 24 hours period? i.e., the 4th oldest post should be more than 24h apart from the most recent.