CODEWITHSUNDEEP
mysql
user2894829
user2894829

Reputation: 815

MySQL default timestamp of insert

I have a table X which comprises 1 default columns to record the time when data gets inserted:

`a` int,
`b` varchar(10),
`audit_create` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)

Then use a scheduled event to run the following statement every 1 minute:

insert into X (a, b) 
select ....

The execution of the statement tables about 20 seconds, so I assume the column audit_create shoud be like:

`2021-06-21 00:00:20.xxx`
`2021-06-21 00:01:21.xxx`
`2021-06-21 00:02:20.xxx`

however, the values of this column seem like when each execution starts to run:

  `2021-06-21 00:00:00.804`
  `2021-06-21 00:01:00.804`
  `2021-06-21 00:02:00.804`

Why the default time values equals when the statement starts rather than when the results are ready?

Upvotes: 0

Views: 493

Answers (2)

Akina
Akina

Reputation: 42844

NOW() and all its aliases (including CURRENT_TIMESTAMP) returns a constant time that indicates the time at which the statement began to execute. By design, noncorrectable.

There is SYSDATE() function which returns the time when it is called, but it cannot be used as default value for TIMESTAMP column. You must use it explicitly in INSERT/UPDATE statement:

insert into X (a, b, audit_create) 
select aa, bb, SYSDATE(3) FROM ...

DEMO

Upvotes: 0

Vilsad P P
Vilsad P P

Reputation: 1569

I think the value is set when the statement is prepared, not while executing the insert statement. If you want the time stamp from the execution time, you will have to mention that explicitly. Like below

insert into X (a, b, audit_create ) 
select valueA, ValueB, Now() from ....

I had faced something similar, but unfortunately i couldn't recollect where i have read the reason for this.

Upvotes: 1

Related Questions