Re: Need help to query with timestamp in C++
Hi Saul, I need to use C++ and I'm not writing a web application. Thanks anyway. Kandy I have done queries to the database in PHP with variables like month but easily can select from a range of time and data to produce the same results, the output goes directly to the web so if that is what you are seeking for, I can help with PHP. Saul Kandy Wong wrote: 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] -- Kandy Wong Scientific Programmer Analyst TRIUMF (Canada's National Laboratory for Particle and Nuclear Physics) 4004 Wesbrook Mall Vancouver, BC, Canada, V6T 2A3 Phone: (604) 222- 1047 ext. 6193 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
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]
Re: Need help to query with timestamp in C++
Kandy Wong wrote: Hi Saul, I need to use C++ and I'm not writing a web application. Thanks anyway. you can do something like: select min(abs(timediff(targettime,timestamp))) from table where condition ; if you use the libmysql you can get the result as strings back (the method i prefer) and convert them in what ever you need. re, wh 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
Kandy Wong wrote: And what is the good connector (C++ to MySQL) to use? MySQL++ (http://tangentsoft.net/mysql++/) has native Date, Time, and DateTime data types. You can convert to these types implicitly: mysqlpp::DateTime dt = row[my_column]; Row::operator[] doesn't return DateTime, it returns a stringish type, which can convert itself to lots of different C++ data types. This is useful because the MySQL C API normally returns results in string form, so you need a natural way to convert these values to the native C++ types for processing. In this particular case, it saves you from having to do the timestamp string parsing yourself. -- Warren Young, maintainer of MySQL++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help to query with timestamp in C++
I'm puzzled by the layout of your table, if that's what you're showing us. Is the timestamp in the table truly associated with the time at which the user put in his approximate time? If, for example, the user types in 04:05:07 at 04:03:02, and then types in 04:02:59 at 04:03:03, what is it that you want to return? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Kandy Wong [mailto:[EMAIL PROTECTED] Sent: Friday, August 15, 2008 2:36 AM To: Saul Bejarano Cc: mysql@lists.mysql.com Subject: Re: Need help to query with timestamp in C++ Hi Saul, I need to use C++ and I'm not writing a web application. Thanks anyway. Kandy I have done queries to the database in PHP with variables like month but easily can select from a range of time and data to produce the same results, the output goes directly to the web so if that is what you are seeking for, I can help with PHP. Saul Kandy Wong wrote: 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] -- Kandy Wong Scientific Programmer Analyst TRIUMF (Canada's National Laboratory for Particle and Nuclear Physics) 4004 Wesbrook Mall Vancouver, BC, Canada, V6T 2A3 Phone: (604) 222- 1047 ext. 6193 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
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]
Re: Need help to query with timestamp in C++
I have done queries to the database in PHP with variables like month but easily can select from a range of time and data to produce the same results, the output goes directly to the web so if that is what you are seeking for, I can help with PHP. Saul Kandy Wong wrote: 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]
