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.
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 |
+---------------------+