Adding TIMESTAMP field to MySQL

Question

I would like to add an 'expires' timestamp column to a MySQL database table, but I don't want it to be updated automatically with the current timestamp.

Basically, I'll be comparing this value e.g. 1650477744 with the current timestamp to see if the entry has expired.

Whenever I try to add such a field defined as a TIMESTAMP, it expects (I think) a formatted timestamp, like 2021-06-01 12:00:00.

How should I define the field?

Given it seems you'll be comparing with a UnixTime, why not store it as a signed 32-bit integer, or signed 64-bit integer if you want to handle past 03:14:07 UTC 2038-01-19? — Will Walsh
– Will Walsh, Commented Jun 1, 2021 at 23:55
@WillWalsh, that's what I eventually did - defined it as UNSIGNED BIGINT. I was hoping that describing it as a TIMESTAMP would ensure that there would be additional low-level checking, but the extra hassle of my (possibly rare) situation probably wasn't worth it. — roryhewitt
– roryhewitt, Commented Jun 3, 2021 at 0:52

Bill Karwin · Accepted Answer · 2021-06-02 00:01:31Z

2

Enable the explicit_defaults_for_timestamp configuration option for your MySQL Server.

Then datetime/timestamp columns won't have the automatic behavior you describe unless you declare the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP explicitly when you define the column. Columns that don't have those clauses won't have that behavior.

Read https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html for more details.

answered Jun 2, 2021 at 0:01

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

1 Comment

roryhewitt Over a year ago

Thanks for the info. Unfortunately I don't have access to those server settings (it's being run from a website server and I only have access to a few settings). Eventually I did as @WillWalsh suggested in his comment above and just defined it as an UNSIGNED BIGINT. FWIW, I read that page, but didn't follow the link to explicit_defaults_for_timestamp

Collectives™ on Stack Overflow

Adding TIMESTAMP field to MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related