using mysql datetime and datetime(6) with c++ and sql

datetime

no fraction part.

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,\
    PRIMARY KEY(`id`)\
    )\
    ENGINE = InnoDB\
    DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci\
    AUTO_INCREMENT = 0\
    ROW_FORMAT = DYNAMIC\
    ;

camera.CreateTime of type time_t

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");

datetime(6)

with fraction part: millisecond,microsecond

Reference

• fractional-seconds
• timestamp-with-a-millisecond-precision-how-to-save-them-in-mysql

History

• 20180118: created.