Reputation: 800
I am trying to create a query that will limit insertion into a table based on the last time the poster sent data to the table.
For example if you posted data to the table then you are locked out of the system for another 10 hours. Here is what I came up with so far. But I get nowhere with the actual results on the data. Any help?
SELECT DATE( `date` )
FROM tablename
WHERE DATE( CURDATE( ) ) < CURDATE( ) - INTERVAL 1002
DAY
LIMIT 0 , 30
Upvotes: 0
Views: 73
Reputation: 425291
This will return a single post from the last 10 hours, if it exists:
SELECT *
FROM tablename
WHERE `date` >= NOW() - INTERVAL 10 HOUR
LIMIT 1
I'm assuming date
is declared as DATETIME
, since actual DATE
does not contain the time part and hence is only day-accurate.
If date
is an integer UNIX timestamp, use this:
SELECT *
FROM tablename
WHERE `date` >= UNIX_TIMESTAMP(NOW() - INTERVAL 10 HOUR)
LIMIT 1
Upvotes: 1
Reputation: 3903
First of all create a Time (TIMESTAMP DEFAULT CURRENT_TIMESTAMP) columnt in your table. It will be automatically set to current date on row insert
Then check:
SELECT COUNT(*) FROM Table WHERE Time > NOW() - INTERVAL 10 HOUR
If its 1 or more - block
You must compare the time last post was put with current time, not current time with current time :|
Upvotes: 0
Reputation: 26333
First of all, you need a DATETIME
column and not a DATE
column. Assuming that tablename.date
is a DATETIME
column, then 10 hours before right now is CURRENT_TIMESTAMP - INTERVAL 10 HOUR
.
Upvotes: 0
Reputation: 13933
There are a number of ways you could do this. Perhaps if you have a user settings table you could simply add a "last_insert" field, and store the timestamp as an integer value- that would be a super simple way to do it- you could check the current timestamp vs user_settings.last_insert and voila! I suppose you could use datetime too. Whatever floats the boat.
Upvotes: 0