store time_t with mysql cpp connector in c++

Guide

create table

CREATE TABLE IF NOT EXISTS `camera` (\
        `id`  int(11) NOT NULL AUTO_INCREMENT,\
        `camera_id`  tinyint(4) UNSIGNED NOT NULL,\
        `create_time`  datetime NOT NULL)

camera.CreateTime of type in c++.

time_t CreateTime;

time_t can at most contain seconds, if we want to get millisecond or microsecond, we can use boost.date_time.
see ref: using boost.date_time to get time in millisecond microsecond level

in sql, we use FROM_UNIXTIME and UNIX_TIMESTAMP to insert and select time_t value.

insert

sql

INSERT INTO camera(camera_id,create_time) VALUES ( ?,FROM_UNIXTIME(?) );

code

pstmt->setUInt64(2, camera.CreateTime); // unix_ts(integer) ===>mysql_ts(string)

select

sql

SELECT id,camera_id,UNIX_TIMESTAMP(create_time) as unix_ts FROM camera;

code

camera.CreateTime = res->getUInt64("unix_ts");

Reference

• instal mysql cpp connector on ubuntu 16.04

History

• 20180122: created.