Hi Dusan,
Thank you so much. It works!
Kandy
> Hi Kandy,
>
> this could be the query you are looking for. It should return record
> with the closest timestamp to your required time:
>
> (SELECT TIMEDIFF('20080815091907', timestamp_column) AS diff, t.* FROM
> table1 t
> WHERE timestamp_column <= '20080815091907'
> ORDER BY timestamp_column DESC LIMIT 1
> )
> UNION
> (SELECT TIMEDIFF(timestamp_column, '20080815091907') AS diff, t.* FROM
> table1 t
> WHERE timestamp_column >= '20080815091907'
> ORDER BY timestamp_column LIMIT 1
> )
> ORDER BY diff LIMIT 1
>
> HTH,
> Dusan
>
> Kandy Wong napsal(a):
>> Hi,
>>
>> I need to write a C++ program in a Linux environment to query with a
>> timestamp.
>> The user will only provide with an approximate time so I'd like to know
>> how can I write a program or a query to return the closest data.
>>
>> The followings are the timestamp in the MySQL database:
>> | 2008-08-05 03:56:09 | 1217933769 |
>> | 2008-08-05 03:56:19 | 1217933779 |
>> | 2008-08-05 03:56:29 | 1217933789 |
>> | 2008-08-05 03:59:39 | 1217933979 |
>> | 2008-08-05 03:59:49 | 1217933989 |
>> | 2008-08-05 03:59:59 | 1217933999 |
>> | 2008-08-05 04:02:39 | 1217934159 |
>> | 2008-08-05 04:02:49 | 1217934169 |
>> | 2008-08-05 04:02:59 | 1217934179 |
>>
>> For example, '2008-08-05 04:01:39' is the time provided by the user
>> which
>> does not exist in the database. So, how can I return the closest data?
>> I
>> know I can make use of 'LIKE' but this will return more than one data.
>> What is the best method to get the closest one?
>> And what is the good connector (C++ to MySQL) to use?
>> Any suggestion?
>> Thank you.
>>
>> Kandy
>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
