I am using MySql and I have a field of type `BIGINT(20).
I realise that I can use CURRENT_TIMESTAMP as default as explained here.
However, how do I assign, by default, a javascript-like timestamp, which includes milliseconds?
I am happy to just have the "000" at the end if there is no other way to have a millisecond-precise timestamp.
UPDATE: Please note that the question is based on leaving the column type as BIGINT(20)
The basic answer to your question is you can't, as BIGINT columns can not have CURRENT_TIMESTAMP as a default value.
If you change your column type to TIMESTAMP(3) it will record timestamps with 3 decimal places of precision (i.e. down to milliseconds). You can have up to 6 decimal places. See the manual. In this situation you will also want to change your default to CURRENT_TIMESTAMP(3).
A workaround to make it appear as if the column is a BIGINT would be to create a VIEW on the table using UNIX_TIMESTAMP for reading e.g.
CREATE VIEW jobs_us AS
SELECT ..., UNIX_TIMESTAMP(added) AS added
FROM jobs
and use INSERT and UPDATE triggers to convert integer values to TIMESTAMP format using FROM_UNIXTIME e.g.
CREATE TRIGGER jobs_added BEFORE INSERT ON jobs
FOR EACH ROW
BEGIN
IF NEW.added IS NOT NULL THEN
SET NEW.added = FROM_UNIXTIME(NEW.added);
END IF;
END
ALTER TABLE jobs ALTER COLUMN added SET DEFAULT CURRENT_TIMESTAMP(3) and that's it?
ALTER TABLE jobs MODIFY added TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)ALTER TABLE jobs MODIFY added TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3); I get ERROR 1292 (22007): Incorrect datetime value: '1553893854362' for column 'added' at row 1. If I run ALTER TABLE jobs MODIFY added BIGINT(20) DEFAULT CURRENT_TIMESTAMP(3); I get ERROR 1067 (42000): Invalid default value for 'added'CURRENT_TIMESTAMP as a default value for a BIGINT column, it can only be a default for a Date or Time type column. See my updated demo for how you can translate to/from unix timestamps using the UNIX_TIMESTAMP and FROM_UNIXTIME functions