MySQL selecting records more than 1 hour old

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
susrisha
Forum Contributor
Posts: 439
Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India

MySQL selecting records more than 1 hour old

Post by susrisha »

Hello ,
I am trying to run a query that fetches me the records which are more than 1 hour old. For this i have created a field in the table with DATE TIME type.
Here is the table definition

Code: Select all

 
    Field           Type       
    slno             bigint(20)[index]                      
    tf_devid    varchar(50)                         
    tf_audio_port   bigint(20)                              
    tf_video_port   bigint(20)                          
    tf_insertion_time   datetime                                
    tf_updated_time datetime    
 
I am running this query to fetch the records which are more than 1 hour old

Code: Select all

 
SELECT TIME_TO_SEC(NOW()-tf_updated_time) , `tf_sdp_filename` FROM tbl_sdp_users WHERE  TIME_TO_SEC(NOW()-tf_updated_time)>3600
 
The problem is sometimes it fetches 3 rows.. and the very next moment it shows none.. Can any one please guide me through this query..
Thanks in advance
User avatar
susrisha
Forum Contributor
Posts: 439
Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India

Re: MySQL selecting records more than 1 hour old

Post by susrisha »

solved it myself. thought it would be useful for others.. so posting the correct query that i am using. There might be a better one. if there is, please let me know

Code: Select all

 
SELECT tf_sdp_filename FROM tbl_sdp_users WHERE TIME_TO_SEC(TIMEDIFF(NOW(),tf_updated_time))>3600
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL selecting records more than 1 hour old

Post by Eran »

Code: Select all

... WHERE updated_time < NOW() - INTERVAL 1 HOUR
Post Reply