CODEWITHSUNDEEP
phpmysqlsql
Laureant
Laureant

Reputation: 1019

Add millisecond precision to MySQL timestamp column in table

There's a log table with a TIMESTAMP column, that currently has precision down to the second.

This is not enough, cause sometimes multiple events happen in the same second, and this way we can't tell the order of the events happening.

I know this could be fixed with an automatically incremented ID (to put them in order of happening), but a Timestamp with millisecond precision is what I'm after since I would like to see how much time happens between two events.

If I change the columns default value from CURRENT_TIMESTAMP to CURRENT_TIMESTAMP(3), I get the following error in MySQL Workbench: 

Executing:
ALTER TABLE `my_db`.`logs` 
CHANGE COLUMN `timestamp` `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ;

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1067: Invalid default value for 'timestamp'
SQL Statement:
ALTER TABLE `my_db`.`logs` 
CHANGE COLUMN `timestamp` `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6)

I already have data in the table, which I wouldn't like to use, so I would like to alter this table.

Is it actually possible to alter the table this way, so from now on the timestamp column will be more precise?

PS: I tried adding a new (totally blank) column with the added precision, but it threw the same error.

Upvotes: 4

Views: 7693

Answers (1)

nl-x
nl-x

Reputation: 11832

Your alter query has the (6) missing on a second spot:

ALTER TABLE `my_db`.`logs` 
CHANGE COLUMN `timestamp` `timestamp` TIMESTAMP(6) NOT NULL DEFAULT 
CURRENT_TIMESTAMP(6)

Upvotes: 10

Related Questions