Thread: TIMEDIFF issue

Hi
I have a field (Search_Time) which stores "minute" in integer.
Now i have to find timediff using that "minute". like

Code:

Select * From tblmessage Where timeDIFF(now(), Message_Time) <= Search_Time

If i hard code time, like this, then it works fine

Code:

Select * From tblmessage Where timeDIFF(now(), Message_Time) <= '00:15:00'

Now the problem is that if say the Search_Time field has value above 60, then this query doesnt work.

Do i have to convert minutes in integer to something like hh:mm:ss, if yes, how??

MySql 4.1
VB.NET 2.0

Micky

Try this function: STR_TO_DATEDate Time FunctionsMySQL Tutorial

Originally Posted by jmurrayhead

Try this function: STR_TO_DATEDate Time FunctionsMySQL Tutorial

Thanx J, let me try and be back

Nopes J
It takes a datetime value in STR_TO_DATE(str, format)

But i have an integer.

I got it

Code:

Select * From tblmessage Where timeDIFF(now(), Message_Time) <= CONCAT(Search_Time / 60, ':', Search_Time - (Search_Time / 60), ':00')

Thanx to sync_or_swim at other place

How about this one, M?

MySQL :: MySQL 5.1 Reference Manual :: 11.6 Date and Time Functions

Just for the records, in MySql 5.1, its quite easy

Code:

Select * From tblmessage Where TIMESTAMPDIFF(minute, Message_Time, now()) <= Search_Time 

Originally Posted by jmurrayhead

How about this one, M?

MySQL :: MySQL 5.1 Reference Manual :: 11.6 Date and Time Functions

J, that converts seconds, but i have minutes

Originally Posted by micky

J, that converts seconds, but i have minutes

lol well, there's also MAKETIME(): MySQL :: MySQL 5.1 Reference Manual :: 11.6 Date and Time Functions

I know one of these functions is going to work...much shorter than typing out S_O_S's solution

Originally Posted by jmurrayhead

lol well, there's also MAKETIME(): MySQL :: MySQL 5.1 Reference Manual :: 11.6 Date and Time Functions

I know one of these functions is going to work...much shorter than typing out S_O_S's solution

I have tried that too J, infact from this page i have tried almost everything

and you know what, SOS solution isnt correct either

I hate MySql