how to automatically initialize the timestamp column in mysql

Question

I am working with MySQL I have made a table which have a TIMESTAMP column, which add current time and date to each row added. But I don't know how to initialize it when I insert data to that table. My SQL Queries are :

TABLE

"CREATE TABLE IF NOT EXISTS messages ( mess_id int PRIMARY KEY AUTO_INCREMENT, mess_from int, mess_to int, mess_txt VARCHAR(20000), mess_time TIMESTAMP, FOREIGN KEY (mess_from) REFERENCES users(id),FOREIGN KEY (mess_to) REFERENCES users(id) )"

INSERTION

"INSERT INTO user_messages (NULL,'"+data.from+"','"+data.to+"','"+data.text+"',DEFAULT)"

The problem is, I don't know how to initialize the timestamp column, I tried NULL and DEFAULT but both show me this error of invalid syntax. I want the timestamp field to be initialized automatically! That's it.

Is this a MySQL question? It appears to be unrelated to SQL Server, which is a different product. You also urgently need to escape your parameters. — tadman
– tadman, Commented Oct 26, 2017 at 17:28
If the column has a defined DEFAULT you just need to exclude the column out of the insert to get the default value. If you want the actual timestamp as default you can also just insert NOW() — Thallius
– Thallius, Commented Sep 7, 2020 at 6:42

Geoduck · Accepted Answer · 2017-10-26 17:46:01Z

2

On the table creation, set the default on the timestamp column to CURRENT_TIMESTAMP.

CREATE TABLE IF NOT EXISTS messages ( 
    mess_id int PRIMARY KEY AUTO_INCREMENT, 
    mess_from int, 
    mess_to int, 
    mess_txt VARCHAR(20000), 
    mess_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    FOREIGN KEY (mess_from) REFERENCES users(id),FOREIGN KEY (mess_to) REFERENCES users(id) )

EDIT: For completeness, this will set the mess_time field to the current timestamp when a new record is inserted and does not contain an explicit value for that field. It will have no effect when an UPDATE on that row occurs. You can set it so that on every update the timestamp will be set to the current timestamp using "ON UPDATE CURRENT_TIMESTAMP"

CREATE TABLE IF NOT EXISTS messages ( 
    mess_id int PRIMARY KEY AUTO_INCREMENT, 
    mess_from int, 
    mess_to int, 
    mess_txt VARCHAR(20000), 
    mess_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    FOREIGN KEY (mess_from) REFERENCES users(id),FOREIGN KEY (mess_to) REFERENCES users(id) )

When doing the INSERT, do not specify the timestamp column:

INSERT INTO user_messages (mess_from,mess_to, mess_text) VALUES (@FromValue,@ToValue,@TextValue)

(The above will work in all cases. There is a system variable that controls whether you can use NULL to insert the current_timestamp. This is not standard and less compatible with other SQL engines, so it is often set to standards mode.

The variable is explicit_defaults_for_timestamp, but it has been deprecated, and these non-standard behaviors are set to be removed in future mysql versions, so I would not recommend using it.)

edited Oct 26, 2017 at 17:46

answered Oct 26, 2017 at 17:31

Geoduck

9,0212 gold badges29 silver badges27 bronze badges

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

4 Comments

Geoduck Over a year ago

sorry, I didn't address the INSERT statement.

Geoduck Over a year ago

edited. The INSERT statement should exclude the timestamp field.

JasonGenX Over a year ago

CURRENT_TIMESTAMP doesn't work. It's not recognized by MYSQL

Geoduck Over a year ago

@JasonGenX CURRENT_TIMESTAMP is supported. dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

Neo Anderson · Accepted Answer · 2020-09-07 06:40:38Z

1

Try the following:

INSERT INTO user_messages (NULL, '"+data.from+"', '"+data.to+"', '"+data.text+"', now());

edited Sep 7, 2020 at 6:40

Neo Anderson

6,4302 gold badges14 silver badges30 bronze badges

answered Oct 26, 2017 at 17:32

Mackson

263 bronze badges

Collectives™ on Stack Overflow

how to automatically initialize the timestamp column in mysql

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related