Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

I have a c++ time double that records seconds since 1 Jan 1970.

I want to convert this double and store it in a MySQL database, so here is my code to convert it firstly to a datetime format: *NOTE: ss.time is the double..

/* do the time conversion */
time_t rawtime;
struct tm * timeinfo;
rawtime = (time_t)ss.time;
timeinfo = localtime(&rawtime);

This code converts it to the following format: Thu Jul 24 05:45:07 1947

I then attempt to write it to the MySQL database as follows:

string theQuery = "UPDATE readings SET chng = 1, time = CAST('";
theQuery += boost::lexical_cast<string>(asctime(timeinfo));
theQuery += "' AS DATETIME) WHERE id = 1";

It does not work, but updates the time DATETIME variable in the table with NULL.

Can someone tell me how to do a correct conversion and update the SQL table?

Your c++ double is just a standard Unix timestamp, which MySQL can convert using its FROM_UNIXTIME function:

UDPATE ... SET ... time=FROM_UNIXTIME(rawtime)
                For some reasons, when I try and update using FROM_UNIXTIME() and put 3586767203 into the brackets, which was the time of the reading, it enters null into the fields?
– Cornel Verster
                Oct 1, 2013 at 6:49
                mysql only supports 32bit unix time_t, which maxes out in early 2038. Your timesetamp is in Aug 2083, far beyond the limit.
– Marc B
                Oct 1, 2013 at 15:34
        

Thanks for contributing an answer to Stack Overflow!

  • Please be sure to answer the question. Provide details and share your research!

But avoid

  • Asking for help, clarification, or responding to other answers.
  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.