DaniWeb Forum Index > Software Development > C++

MySQL result set and time.h

Please support our C++ advertiser: Programming Forums - DaniWeb Sister Site

Thread Solved

•

Join Date: Aug 2008

Posts: 4

Reputation: xcesmess is an unknown quantity at this point

Solved Threads: 0

xcesmess

Offline

Newbie Poster

MySQL result set and time.h

Aug 19th, 2008

I'm fairly new to the C++ world (did some C work in the past years ago) so I'm a little rusty on some things

I've got a small table in a database and I'm trying to grab some date information out of a UNIX EPOCH time stamp (all seconds). I've tried tackling this with time.h but I can't seem to find anything in time.h that allows a 'from string' type of approach so I thought I'd ask for some advice.

My database is on MySQL 5 (5.0.45) and is on a Fedora box. The table looks like this:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
Table: weather
+-------------+---------+------+-----+---------+-------+
| FIELD       | Type    | NULL | Key | DEFAULT | Extra |
+-------------+---------+------+-----+---------+-------+
| id          | CHAR(4) | NO   | PRI |         |       |
| last_update | INT(11) | YES  |     | 0       |       |
+-------------+---------+------+-----+---------+-------+
Table: weather
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| id          | char(4) | NO   | PRI |         |       |
| last_update | int(11) | YES  |     | 0       |       |
+-------------+---------+------+-----+---------+-------+

The data looks like this:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
mysql> SELECT * FROM site WHERE id = 'kict';
+------+-------------+
| id   | last_update |
+------+-------------+
| kict |  1218689222 |
+------+-------------+
1 row IN SET (0.07 sec)
mysql> select * from site where id = 'kict';
+------+-------------+
| id   | last_update |
+------+-------------+
| kict |  1218689222 |
+------+-------------+
1 row in set (0.07 sec)

The date string you see in the table is pulled via cURL as a the FILETIME. I checked to see if you could specify the date format that it supplies but this is not possible (as far as I could tell).

What I am trying to accomplish is taking the date from the above database and pulling out certain pieces such as the month, day and year.

Here is why code so far with my 'rough' implementation but it isn't working.

 Help with Code Tags 
 c++ Syntax (Toggle Plain Text) 
#include <iostream>
#include <iomanip>
#include <mysql++.h>
#include <time.h>
 
using namespace std;
 
char dbHost[20] = "localhost";
char dbUser[20] = "***";
char dbPass[20] = "***";
char dbName[20] = "weather";
 
int main(int argc, char *argv[])
{
        // Connect to the DB
        mysqlpp::Connection conn(false);
        if (!conn.connect(dbName, dbHost, dbUser, dbPass)) {
                cerr << "DB Connection Failed: " << conn.error() << endl;
                return 1;
        }
 
        // Query
        mysqlpp::Query query = conn.query("select id,last_update,from_unixtime(last_update) from site");
        if (mysqlpp::StoreQueryResult res = query.store())
        {
                for (int i = 0; i < res.num_rows(); ++i) {
 
                        // Here is where I want to convert the time from the result set
 
                        cout << res[i][0] << " - " << res[i][1] << endl;
                }
        }
        else
        {
                cerr << "Failed to get item list: " << query.error() << endl;
                return 1;
        }
        return 0;
}
#include <iostream>
#include <iomanip>
#include <mysql++.h>
#include <time.h>

using namespace std;

char dbHost[20] = "localhost";
char dbUser[20] = "***";
char dbPass[20] = "***";
char dbName[20] = "weather";

int main(int argc, char *argv[])
{
        // Connect to the DB
        mysqlpp::Connection conn(false);
        if (!conn.connect(dbName, dbHost, dbUser, dbPass)) {
                cerr << "DB Connection Failed: " << conn.error() << endl;
                return 1;
        }

        // Query
        mysqlpp::Query query = conn.query("select id,last_update,from_unixtime(last_update) from site");
        if (mysqlpp::StoreQueryResult res = query.store())
        {
                for (int i = 0; i < res.num_rows(); ++i) {

                        // Here is where I want to convert the time from the result set

                        cout << res[i][0] << " - " << res[i][1] << endl;
                }
        }
        else
        {
                cerr << "Failed to get item list: " << query.error() << endl;
                return 1;
        }
        return 0;
}

Could someone give me some pointers? Like I said, I'm at wit's end here trying to figure this out!

Thanks!
Kelly

•

Join Date: Dec 2005

Posts: 5,850

Reputation: Salem has a reputation beyond repute

Solved Threads: 751

Salem

Offline

Void main'ers are DOOMed

Re: MySQL result set and time.h

Aug 19th, 2008

http://www.opengroup.org/onlinepubs/...localtime.html
Use localtime() or gmtime() as appropriate to turn a "time_t" into a "struct tm".
Use mktime() to go the other way
Use strftime() to format bits of a struct tm

•

Join Date: Aug 2008

Posts: 4

Reputation: xcesmess is an unknown quantity at this point

Solved Threads: 0

xcesmess

Offline

Newbie Poster

Re: MySQL result set and time.h

Aug 19th, 2008

Salem:

Thanks for the tips.. I've been to that website before and see that I might be able to use the time() command but I'm struggling to make it work.

For instance I see that I can use the time(NULL) or time(0) command to return the current time and set this as a time_t variable. However if I try to do the following:

 Help with Code Tags 
 c++ Syntax (Toggle Plain Text) 
for (int i =0; i < res.num_rows(); ++i) {
  time_t now = time(res[i][1]);
  struct tm * timeinfo = localtime(&now);
  cout << res[i][0] << " - " << res[i][1] << asctime(timeinfo) << endl;
}
for (int i =0; i < res.num_rows(); ++i) {
  time_t now = time(res[i][1]);
  struct tm * timeinfo = localtime(&now);
  cout << res[i][0] << " - " << res[i][1] << asctime(timeinfo) << endl;
}

I get the following nasty error message:

 Help with Code Tags 
 C++ Syntax (Toggle Plain Text) 
mysql.cpp:28: error: conversion from 'const mysqlpp::String' to 'time_t*' is ambiguous
mysql.cpp:28: error: conversion from 'const mysqlpp::String' to 'time_t*' is ambiguous

I'm guessing the variable types aren't correct but I'm not sure exactly how to cast them or convert them... probably bad words but I'm still learning

Kelly

•

Join Date: Dec 2005

Posts: 5,850

Reputation: Salem has a reputation beyond repute

Solved Threads: 751

Salem

Offline

Void main'ers are DOOMed

Re: MySQL result set and time.h

Aug 20th, 2008

Your results are time_t's (or should be)
struct tm * timeinfo = localtime(&res[i][1]);

•

Join Date: Aug 2005

Posts: 15,672

Reputation: Ancient Dragon has a reputation beyond repute

Solved Threads: 1502

Ancient Dragon Ancient Dragon is offline

Offline

Still Learning

Re: MySQL result set and time.h

Aug 20th, 2008

>>time_t now = time(res[i][1]);
That's wrong -- the parameter to time is a pointer to a time_t object. I don't know what you are passing but it isn't time_t*

If you are attempting to convert the date/time in the result set to time_t, that is the wrong way to do it. win32 api FileTimeToSystemTime() will convert that FILETIME object to a SYSTEMTIME structure, which is similar to struct tm, but has milliseconds, and you can easily access the month, day, year, hour, minutes, seconds, and milliseconds.

Last edited by Ancient Dragon; Aug 20th, 2008 at 9:56 am.

Don't PM me with questions -- you might get a nasty PM in response. If you have a question then post it in one of the forums.