List:General Discussion« Previous MessageNext Message »
From:Dušan Pavlica Date:August 15 2008 7:29am
Subject:Re: Need help to query with timestamp in C++
View as plain text  
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
>
>
>   
Thread
Need help to query with timestamp in C++Kandy Wong15 Aug
  • Re: Need help to query with timestamp in C++Saul Bejarano15 Aug
    • Re: Need help to query with timestamp in C++Kandy Wong15 Aug
      • Re: Need help to query with timestamp in C++walter harms15 Aug
      • RE: Need help to query with timestamp in C++Jerry Schwartz15 Aug
  • Re: Need help to query with timestamp in C++Dušan Pavlica15 Aug
    • Re: Need help to query with timestamp in C++Kandy Wong15 Aug
  • Re: Need help to query with timestamp in C++Warren Young15 Aug