How to get DATETIME and store to time_t using MySQL Connector/C++?

Question

I'm using MySQL Connector/C++ as library to get result from MySQL database. I'm using C++11 standard. I want to get a DATETIME field (named jointime) from database and store it as time_t variable in follow:

#include <cstdlib>
#include <iostream>
#include <ctime>
#include "mysql_connection.h"
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
using namespace std;
int main(void)
{
    auto driver = sql::mysql::get_mysql_driver_instance();
    auto con = driver->connect("tcp://127.0.0.1:3306", "user", "password");
    con->setSchema("mydb");
    auto stmt = con->createStatement();

    auto res = stmt->executeQuery("SELECT * from users;");
    while (res->next()) {
        string username = res->getString("username");
        time_t jt ; // res->get???
        ...
    }
    delete res;
    delete stmt;
    delete con;
}

How should I get jointime and assign it to time_t jt?

There isn't getDateTime() or relative method.

Answer 1

MySQL Connector/C++ returns DATATIME as string. The output format is %Y-%m-%d %H:%M:%S so you need to write following function to convert this string to time_t:

time_t String2time_t(const string& strDateTime){
    tm t;
    strptime(strDateTime.c_str(), "%F %T", &t);
    return mktime(&t); 
}

Now use this line to get DATETIME and save it in a time_t:

time_t jt =  String2time_t((string)res->getString("jointime"));

Note that the output of getString is SQLstring not std::string so you need convert it to std::string before passing to the written function.

Answer 2

MySQL Connector/C++ returns DATATIME as string. The output format is %Y-%m-%d %H:%M:%S so you need to write following function to convert this string to time_t:

time_t String2time_t(const string& strDateTime){
    tm t;
    strptime(strDateTime.c_str(), "%F %T", &t);
    return mktime(&t); 
}

Now use this line to get DATETIME and save it in a time_t:

time_t jt =  String2time_t((string)res->getString("jointime"));

Note that the output of getString is SQLstring not std::string so you need convert it to std::string before passing to the written function.