prog_24
prog_24

Reputation: 800

How can I get the date difference of a timestamp

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

Answers (4)

Quassnoi
Quassnoi

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

peku33
peku33

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

Ed Gibbs
Ed Gibbs

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

dudewad
dudewad

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

Related Questions