CODEWITHSUNDEEP
mysqldatetimesequelize.jsmilliseconds
jamesdeath123
jamesdeath123

Reputation: 4606

mysql updated_at use milliseconds since EPOCH as default ON UPDATE

In my app I am using Sequelize. Now I want to use milliseconds since EPOCH for the updated_at column, so that it looks like:

-------------
updated_at
-------------
1571838511364
-------------

The data definition now looks like:

CREATE TABLE `projects` (
   `id` char(36) NOT NULL,
   `name` varchar(45) NOT NULL,
   `description` varchar(450) NOT NULL,
   `active` tinyint(1) NOT NULL DEFAULT '1',
   `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I have done this to the created_at as it is easy to set the default value with Sequelize, but for updated_at, how do I set the default value for ON UPDATE?

I know SELECT UNIX_TIMESTAMP(current_timestamp()) will give me the current millisecond, so I have:

updatedAt: {
      type: Sequelize.STRING,
      allowNull: false,
      defaultValue: Sequelize.literal(
        'UNIX_TIMESTAMP(current_timestamp()) ON UPDATE UNIX_TIMESTAMP(current_timestamp())'
      ),
    },

And this will give me error:

Executing:
ALTER TABLE `ab`.`projects` 
CHANGE COLUMN `updated_at` `updated_at` VARCHAR(45) NOT NULL DEFAULT UNIX_TIMESTAMP(current_timestamp()) ON UPDATE UNIX_TIMESTAMP(current_timestamp()) ;

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNIX_TIMESTAMP(current_timestamp()) ON UPDATE UNIX_TIMESTAMP(current_timestamp()' at line 2

I also tried default value to be: 'UNIX_TIMESTAMP ON UPDATE UNIX_TIMESTAMP' but I got the same sql error anyway.

How do I achieve my goal so that ON UPDATE will use the current millisecond value?

Upvotes: 0

Views: 1592

Answers (1)

EchoMike444
EchoMike444

Reputation: 1692

default value in mysql must a be literal , only exception is using CURRENT_TIMESTAMP() .

https://dev.mysql.com/doc/refman/5.6/en/data-type-defaults.html#data-types-defaults-explicit

unix_timestamp(CURRENT_TIMESTAMP()) is different than CURRENT_TIMESTAMP()

If you want milli-seconds or microsecond you must add a precision to datetime and current_timestamp .

You table will become this with a precision of 4 digits .

CREATE TABLE `projects` (
   `id` char(36) NOT NULL,
   `name` varchar(45) NOT NULL,
   `description` varchar(450) NOT NULL,
   `active` tinyint(1) NOT NULL DEFAULT '1',
   `created_at` datetime(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4),
   `updated_at` datetime(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4),
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  ;

A example with sqlfiddle ( http://sqlfiddle.com/#!9/3a85ff/1 )

Upvotes: 1

Related Questions