store fractional seconds in mysql with boost and c++

cpp

Publish Date: 2018-01-22

Word Count: 587

Read Times: 4 Min

Read Count:

Guide

for only storing seconds time_t in mysql with c++, see guide to store time_t with mysql cpp connector in c++.

fractional second part

From Mysql 5.6.4 then on，date,datetime,timestamp support fractional second part (fsp), with up to microseconds (6 digits) precision:

create table

To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

The fsp value, if given, must be in the range 0 to 6, by default is 0.

rounding

Inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding, as shown in this example:

use time format YYYY-MM-DD HH:MM:SS[.fraction]

insert

use mydb;

DROP TABLE IF EXISTS fractest ;
CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(3), c3 TIMESTAMP(6) );

INSERT INTO fractest 
VALUES ('17:51:04.777666', '2014-09-08 17:51:04.777666', '2014-09-08 17:51:04.777666');

select

mysql> select * from fractest;
+-------------+-------------------------+----------------------------+
| c1          | c2                      | c3                         |
+-------------+-------------------------+----------------------------+
| 17:51:04.78 | 2014-09-08 17:51:04.778 | 2014-09-08 17:51:04.777666 |
+-------------+-------------------------+----------------------------+
1 row in set (0.00 sec)

No warning or error is given when such rounding occurs.

how to generate fsp time

Functions that take temporal arguments accept values with fractional seconds. Return values from temporal functions include fractional seconds as appropriate.

For example, NOW() with no argument returns the current date and time with no fractional part, but takes an optional argument from 0 to 6 to specify that the return value includes a fractional seconds part of that many digits.

now() or now(6)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-01-22 20:58:31 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2018-01-22 20:58:37.223305 |
+----------------------------+
1 row in set (0.00 sec)

store and select with c++

table

we use DATETIME(6) in mysql to store fsp.

CREATE TABLE fractest( create_time DATETIME(6));

insert

both format are ok to insert to mysql,we use to_iso_extended_string in c++ code to get string 2014-09-08T17:51:04.777668.

sql

#std::string to_iso_string(ptime)         : YYYYMMDDTHHMMSS,fffffffff      20180908T175104.777668
#std::string to_iso_extended_string(ptime): YYYY-MM-DDTHH:MM:SS,fffffffff  2014-09-08T17:51:04.77766

INSERT INTO fractest VALUES ('2014-09-08T17:51:04.777668');

code

# 2014-09-08T17:51:04.777668
std::string iso_extended_string = boost::posix_time::to_iso_extended_string(camera.CreateTime);
pstmt->setString(1, iso_string);

select

we need to select datetime as string, because we must keep fsp,we use concat("",create_time) as create_time to get string time 2014-09-08 17:51:04.777668, and then process with c++ to get boost ptime.

sql

# select datetime as string 
# (1) concat("",create_time) as create_time     [we can have fsp]
# (2) DATE_FORMAT(create_time,'%Y-%m-%d %h:%m:%s') AS create_time  [we can not have fsp]

select id, create_time,concat("",create_time) as create_time from camera where id = 1;

code

std::string mysql_string = res->getString("create_time");
// 2014-09-08 17:51:04.777668 ===> 2014-09-08T17:51:04.777668
camera.CreateTime = Util::from_mysql_string(mysql_string);

different datetime string

filename_string 20180118_111501_208797 
iso_string      20180118T111501.208797  ===> from_iso_string

mysql_string           2014-09-08 17:51:04.777668
ios_extended_string    2014-09-08T17:51:04.777668  ===> from_iso_extended_string