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.