How to get a field with the type of timestamp in m

Question

I create a table a_tb using the following sql in mysql command line:

create table a_tb( id int not null auto_increment, w_time timestamp default current_timestamp,a int default -1, PRIMARY KEY ( id ));
+--------+-----------+------+-----+-------------------+----------------+
| Field  | Type      | Null | Key | Default           | Extra          |
+--------+-----------+------+-----+-------------------+----------------+
| id     | int(11)   | NO   | PRI | NULL              | auto_increment |
| w_time | timestamp | NO   |     | CURRENT_TIMESTAMP |                |
| a      | int(11)   | YES  |     | -1                |                |
+--------+-----------+------+-----+-------------------+----------------+

And I use insert into a_tb (a) values (2); Then I get:

+----+---------------------+------+
| id | w_time              | a    |
+----+---------------------+------+
|  1 | 2016-11-22 17:09:34 |    2 |
+----+---------------------+------+

What I want to do is to read data from a_tb by using C++ through ODBC. I have read msdn and successfully have the access to get the data from the table by using the code like Connecting to a MySQL server using C++. Each field is ok except timestamp, which I always get some bizarre numbers. And what I have tried is as follows:

SQLTIMESTAMP tt[14];
SQLGetData(hstmt, 2, SQL_C_TIMESTAMP, tt, 0, &cbTestInt);

and I get

in the console and

time_t tt;
SQLGetData(hstmt, 2, SQL_C_TIMESTAMP, &tt, 0, &cbTestInt);

I get 4222219140663264. and

SQLINTEGER sTestInt, cbTestStr;
SQLGetData(hstmt, 2, SQL_C_ULONG, &sTestInt, 0, &cbTestInt);

I get 2016.

But what I want to get is 2016-11-22 17:09:34. I am puzzled through trying many method...

Answer 1

That number is the numerical representation of the timestamp. It is the number of seconds past since (I believe) 1970-01-01. If it is not an integer, but a real number, the value after the . is the number of microseconds.

Now that we understand the beast, let's see how to fight it. You could implement a function which gathers the year, month, day, hour, minute and second from that number, but that would be an unintuitive solution. Instead, you could use strftime to convert that number into date.