Convert MySQL Datetime to C++ std::time_t

Question

I am stuck in a problem and really hope you can help me. I like to convert a MySQL DATETIME field into a C++ std::time_t variable. My database looks like this:

CREATE TABLE data(
    id              INTEGER       AUTO_INCREMENT UNIQUE,
    path            VARCHAR(1000),
    acquisitionDate DATETIME
);

And I am trying to save the value from acquisitionDate into a c++ variable of type std::time_t.

After some web searching I learned that I have to use UNIX_TIMESTAMP in my MySQL statement. However, I did not understand how to actual use it. I can generate a query and receive a sql::ResultSet:

std::unique_ptr<sql::Statement> stmt(mConnection->createStatement());

std::string query="SELECT id, path, UNIX_TIMESTAMP(acquisitionDate) FROM data WHERE id = 1";

std::unique_ptr<sql::ResultSet> res(stmt->executeQuery(query));

But I don't understand how I can get the actual field value into a std::time_t variable

if(res->next())
{
    std::time_t acquisitionDate =  res->getInt("acquisitionDate");
    std::time_t acquisitionDate =  res->???
}

Somehow my brain does not get this last step. Please help me. Thank you very much!

score 1 · Accepted Answer · answered Jul 07 '16 at 11:32

I also understood that it is possible to access the fields output by:

std::time_t acquisitionDate =  res->getInt("UNIX_TIMESTAMP(acquisitionDate)");

Or the SQL statement could rename the field:

SELECT UNIX_TIMESTAMP(acquisitionDate) AS foo FROM data WHERE id = 1

and then

std::time_t acquisitionDate =  res->getInt("foo");

However, casting should also be a good idea (after you have the correct field name):

std::time_t acquisitionDate =  static_cast<std::time_t>(res->getInt("foo"));

You can also use an alias `UNIX_TIMESTAMP(acquisitionDate) AS acquisitionDate` or get the int by index `res->getInt(2)`. — Minding, Nov 15 '18 at 19:40

score 0 · Answer 2 · answered Jun 29 '16 at 13:53

0

Just cast it

std::time_t acquisitionDate = (std::time_t)res->getInt("acquisitionDate");

answered Jun 29 '16 at 13:53

Inline

2,566
1
16
32

Thanks a lot for your answer. Casting is a good and quite easy idea. However, I figured out that my problem was understanding how to access the returned field. See my answer to this question – Michael Jul 07 '16 at 11:34

Convert MySQL Datetime to C++ std::time_t

2 Answers2