MySQL default timestamp of insert

Question

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?

Vilsad P P · Accepted Answer · 2021-06-21 09:38:52Z

1

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.

answered Jun 21, 2021 at 9:38

Vilsad P P

1,56914 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Akina Over a year ago

NOW() is not safe and will produce the same issue.

Akina · Accepted Answer · 2021-06-21 09:53:33Z

0

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

edited Jun 21, 2021 at 9:53

answered Jun 21, 2021 at 9:46

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

MySQL default timestamp of insert

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related