Page 1 of 1

MySQL selecting records more than 1 hour old

Posted: Fri Aug 07, 2009 11:34 pm
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

Re: MySQL selecting records more than 1 hour old

Posted: Thu Aug 13, 2009 11:48 pm
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
 

Re: MySQL selecting records more than 1 hour old

Posted: Fri Aug 14, 2009 5:40 am
by Eran

Code: Select all

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