MySQL result set and time.h

[xcesmess]

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:

Table: weather
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| id          | char(4) | NO   | PRI |         |       |
| last_update | int(11) | YES  |     | 0       |       |
+-------------+---------+------+-----+---------+-------+

The data looks like this:

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.

#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

[Salem]

http://www.opengroup.org/onlinepubs/009695399/functions/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

[xcesmess]

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:

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:

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

[Salem]

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

[Ancient Dragon]

>>time_t now = time(res[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.