0

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)

xmaster
  • 1,042
  • 7
  • 20
Merc
  • 16,277
  • 18
  • 79
  • 122

1 Answers1

2

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

Demo on dbfiddle

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
Nick
  • 138,499
  • 22
  • 57
  • 95
  • So just `ALTER TABLE jobs ALTER COLUMN added SET DEFAULT CURRENT_TIMESTAMP(3)` and that's it? – Merc Apr 01 '19 at 07:31
  • It should be `ALTER TABLE jobs MODIFY added TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3)` – Nick Apr 01 '19 at 07:32
  • But I wanted the column to stay as a BIGINT(20) rather than timestamp(3)... If I run `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'` – Merc Apr 01 '19 at 07:43
  • @Merc you can't specify `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 – Nick Apr 01 '19 at 07:52
  • But you can't assign a function as default right...? – Merc Apr 01 '19 at 07:57
  • @Merc that is correct, MySQL doesn't allow functions in default values. – Nick Apr 01 '19 at 07:58
  • So the answer to my original question, really, is "you can't"? – Merc Apr 04 '19 at 03:16
  • @Merc indeed. About the only thing you can do is use a timestamp data type and create a view which fetched that as a unix timestamp e.g. `CREATE VIEW jobs_us AS SELECT ..., UNIX_TIMESTAMP(added) AS added FROM jobs` and then use a trigger to convert timestamp values on insert/update using `FROM_UNIXTIME`. – Nick Apr 04 '19 at 05:17
  • Can you add this to the answer? I will accept it straight after that – Merc Apr 05 '19 at 12:08