What is the best way to store CTime in mysql database?

Question

Using the shell command I get the following output with the CTime for a particular file: 1665579792.879379. Now I'm thinking about the best way to save this value to msql database, and what type of column to create. Because when I do something like this:

CREATE TABLE IF NOT EXISTS `test` (
`ctime` float NOT NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

I lose all the digits after the dot.

Answer 1

mysql> create table mytable ( d datetime(6) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into mytable set d = from_unixtime(1665579792.879379);
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytable;
+----------------------------+
| d                          |
+----------------------------+
| 2022-10-12 06:03:12.879379 |
+----------------------------+

The digits after the dot are normal. They are microseconds.

If you don't want to store microseconds, use datetime instead of datetime(6). This will truncate the fractional part of the seconds, and store just the time in whole seconds.

mysql> create table mytable ( d datetime );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into mytable set d = from_unixtime(1665579792.879379);
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytable;
+---------------------+
| d                   |
+---------------------+
| 2022-10-12 06:03:13 |
+---------------------+