Is there a reason to use CURRENT_TIMESTAMP in inserts/updates ?

Question

I was using phpmyadmin to insert some dummy data into a table, and noticed it structured the insert like this:

INSERT INTO `arc`.`transactions` (
    `txn_id` ,
    `date_time`  )
VALUES (
    '50005',
    CURRENT_TIMESTAMP );

Normally, I'd just not include the field "date_time" and the value is created by mySQL (as the field is of type timestamp), and auto-updates whenever you insert or update a record.

Is there any reason to structure the query as you see it above, explicitly inserting CURRENT_TIMESTAMP? Or is this a phpmyadmin oddity?

Answer 1

You can have TIMESTAMP fields that don't auto-update (see the TIMESTAMP properties page for how to specify these), so depending on how you define the column, it might not be the case that an UPDATE query automatically adjusts the TIMESTAMP field.

PHPMyAdmin is probably taking the safe approach here and specifying the value to ensure it's updated, no matter the column definition. PHPMyAdmin can probably detect the default value if it wants to, so another possible explanation would be compatibility between various server versions and modes for any SQL that it generates.

Answer 2

It depends solely on date_time column definition. if it is like

`date_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Then you surely don't need to specify anything when inserting or updating a row, but if it is:

`date_time` TIMESTAMP NOT NULL

Then you have to specify something every time you create or update a row

Answer 3

It could also be a matter of compatibility. CURRENT_TIMESTAMP is defined by the SQL standard. Automatically updated timestamp columns are not. An app that wants to be portable and record timestamps is better off explicitly specifying the currrent timestamp. And what better way to do that than to use the standard, built-in CURRENT_TIMESTAMP function?