MySQL Automatically Store Record Creation Timestamp

mysql automatically store record creation timestamp

Set the DEFAULT constraint to use CURRENT_TIMESTAMP:

CREATE TABLE ...
  your_date_column DATETIME DEFAULT CURRENT_TIMESTAMP
  ...

For an existing table, use the ALTER TABLE statement:

ALTER TABLE your_table
ALTER COLUMN date_column SET DEFAULT CURRENT_TIMESTAMP

Unless you specify a value to for the date_column, the default will be the date & time the INSERT statement was run. NULL and DEFAULT or valid values to use the default constraint otherwise, assuming the column is nullable.

MySQL: Is there a way to automatically set datetime field to record creation timestamp?

Use a trigger to set the default.

DELIMITER |

CREATE
    TRIGGER trigger_name BEFORE INSERT ON tbl_name FOR EACH ROW 
BEGIN
    SET NEW.colname = NOW();
END; 

|

Try this one, including the delimiter.

Automatically set a Javascript timestamp (with milliseconds) when creating a record

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

When is a timestamp (auto) updated?

Give the command SHOW CREATE TABLE whatever

Then look at the table definition.

It probably has a line like this

logtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

in it. DEFAULT CURRENT_TIMESTAMP means that any INSERT without an explicit time stamp setting uses the current time. Likewise, ON UPDATE CURRENT_TIMESTAMP means that any update without an explicit timestamp results in an update to the current timestamp value.

You can control this default behavior when creating your table.

Or, if the timestamp column wasn't created correctly in the first place, you can change it.

ALTER TABLE whatevertable
     CHANGE whatevercolumn 
            whatevercolumn TIMESTAMP NOT NULL
                           DEFAULT CURRENT_TIMESTAMP 
                           ON UPDATE CURRENT_TIMESTAMP;

This will cause both INSERT and UPDATE operations on the table automatically to update your timestamp column. If you want to update whatevertable without changing the timestamp, that is,

To prevent the column from updating when other columns change

then you need to issue this kind of update.

UPDATE whatevertable
   SET something = 'newvalue',
       whatevercolumn = whatevercolumn
 WHERE someindex = 'indexvalue'

This works with TIMESTAMP and DATETIME columns. (Prior to MySQL version 5.6.5 it only worked with TIMESTAMPs) When you use TIMESTAMPs, time zones are accounted for: on a correctly configured server machine, those values are always stored in UTC and translated to local time upon retrieval.

Having both a Created and Last Updated timestamp columns in MySQL 4.0

From the MySQL 5.5 documentation:

One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

Changes in MySQL 5.6.5:

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.

MariaDB/MySql: Setting CURRENT_TIMESTAMP on CREATE and changing noting on UPDATE

you can create trigger for this

DELIMITER //

CREATE TRIGGER user_prefs_before_insert
BEFORE INSERT
   ON user_prefs FOR EACH ROW

BEGIN
   SET NEW.updated = new.created;
END; //

DELIMITER ;

then another trigger for update

DELIMITER //

CREATE TRIGGER user_prefs_before_update
BEFORE UPDATE
   ON user_prefs FOR EACH ROW

BEGIN
   SET NEW.updated = CURRENT_TIMESTAMP();
END; //

DELIMITER ;

How to automatically store current system time on record generation in MySQL?

but I don't think this is valid SQL syntax.

How? It's absolutely valid; Did you actually tried running that? If not then see here http://sqlfiddle.com/#!9/e0b1a7/1

CREATE TABLE purchase_log (order_id int not null, 
order_name varchar(10),
orderdate DATETIME NOT NULL DEFAULT Now()
);

insert into purchase_log(order_id, order_name) values(1,'laptop');

How to create mysql table with column timestamp default current_date?

Use CURRENT_TIMESTAMP function instead of CURRENT_DATE() function

Try this:

DROP TABLE IF EXISTS `visitors`;
CREATE TABLE `visitors` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip` VARCHAR(32) NOT NULL,
  `browser` VARCHAR(500) NOT NULL,
  `version` VARCHAR(500) NOT NULL,
  `platform` ENUM('w','l','m') NOT NULL,
  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `person` (`ip`,`date`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

---

Related Topics