MySQL how to use current time if value of timestamp field is explicitly null? [duplicate]

Question

I want to accomplish the following:

I field in a table in MySQL that, when provided with a NULL value, should resolve to the current time.

I did the following:

CREATE TABLE foo (
    bar TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    baz INT
);

-- Now let's insert something.
INSERT INTO foo (bar, baz) VALUES (null, 2);

-- Now let's make sure it works as desired.
SELECT * FROM foo;

but unfortunately it doesn't work as expected:

**********
bar  | baz
**********
null | 2

It only works when I leave off the column's value in an INSERT statement.

How can I make the above INSERT statement work? That is, the field should be set to the default time when the value is null.

What you're asking isn't possible using only declarative features in SQL: a DEFAULT constraint is only used when the column is omited from an INSERT (or use DEFAULT for the value) - so in order to replace or override values in an INSERT or UPDATE you'll need to use a TRIGGER (which is non-declarative and introduces problems of its own). Instead, why not make bar a NOT NULL column and ensure all INSERT statements omit that column? Or use the DEFAULT keyword (if MySQL supports it, I'm unsure) as: INSERT INTO foo ( bar, baz ) VALUES ( DEFAULT, 123 ) — Dai
– Dai, Commented Jun 21, 2023 at 15:43
@Dai You should post an answer if that's your answer. If you only post a comment, the question remains "unanswered" forever, and anyone who tries to post an answer will be accused of just copying your comment. — Bill Karwin
– Bill Karwin, Commented Jun 21, 2023 at 15:45

Dai · Accepted Answer · 2023-06-21 15:50:46Z

2

What you're asking isn't possible using only declarative features in SQL: a DEFAULT constraint is only used when the column is omited from an INSERT (or uses DEFAULT for the value) - otherwise the only other way (that I'm aware-of) to replace or override values in an INSERT or UPDATE requires using a TRIGGER (which is non-declarative and introduces problems of its own).

Instead, why not make bar a NOT NULL column and ensure all INSERT statements omit that column? Or change your INSERT statements to use the DEFAULT keyword?

Like so:

INSERT INTO foo ( bar, baz )
VALUES ( DEFAULT, 123 )

answered Jun 21, 2023 at 15:50

Dai

158k31 gold badges314 silver badges438 bronze badges

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

1 Comment

coderboy Over a year ago

I didn't know about the DEFAULT keyword in insertion.

Collectives™ on Stack Overflow

MySQL how to use current time if value of timestamp field is explicitly null? [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related