Default value for "microtime" column in MySQL

Question

My interest is to store the time with FSP of 6. As I've read, this cannot be achieved with TIMESTAMP or DATETIME data types. So, I have a double field to store the output of the microtime function.

Is there anyway I can set (or even write some code to create) a default value for such a field?

I want to use something like NOW(6) and get 1442059062.065123 for example.

Answer 1

You could format it with UNIX_TIMESTAMP like below:

mysql> SELECT UNIX_TIMESTAMP(NOW(6));
+------------------------+
| UNIX_TIMESTAMP(NOW(6)) |
+------------------------+
| 1442068528.543100 |
+------------------------+
1 row in set (0,00 sec)
mysql> 

You can find any others date and time functions here . You could add a trigger and if you want a default value for your decimal(16, 6) microtime, use BEFORE INSERT and replace your NEW.bigintvalue=UNIX_TIMESTAMP(NOW(6));.

Example:

mysql> CREATE TABLE `test`.`test1` (
 -> `id` INT NOT NULL AUTO_INCREMENT,
 -> `time` DECIMAL(16, 6) NULL,
 -> PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.01 sec)
mysql> 
mysql> DELIMITER //
mysql> CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`test1_BEFORE_INSERT` BEFORE INSERT ON `test1` FOR EACH ROW
 -> BEGIN
 -> SET NEW.time=UNIX_TIMESTAMP(NOW(6));
 -> END
 -> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> 
mysql> # Adding 1 into id field
mysql> INSERT INTO `test`.`test1` (`id`) VALUES ('1');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test.test1;
+----+-------------------+
| id | time |
+----+-------------------+
| 1 | 1442069359.675330 |
+----+-------------------+
1 row in set (0.00 sec)
mysql> 

• Victor: Isn't there anyway of saying UNIX_TIMESTAMP(NOW(6)) as the default for the field?

Answer 2

Perhaps you want DOUBLE? Or DECIMAL(16,6)?

SELECT UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(NOW(6));
+-----------------------+------------------------+
| UNIX_TIMESTAMP(NOW()) | UNIX_TIMESTAMP(NOW(6)) |
+-----------------------+------------------------+
| 1442099776 | 1442099776.196746 |
+-----------------------+------------------------+

Or multiply by 1000000 and use BIGINT?

SELECT 1000000 * UNIX_TIMESTAMP(NOW(6));
+----------------------------------+
| 1000000 * UNIX_TIMESTAMP(NOW(6)) |
+----------------------------------+
| 1442099987061904.000000 |
+----------------------------------+

(the decimal places will vanish when storing into a BIGINT.)

• Victor: Although this is the way I want to store dates (with 6 decimals), @oNare's answer is the one that fits me, because he showed me how to add the DEFAULT value definer for my field