Amateur Amateur - 9 months ago 40
MySQL Question

Trying to create a MySQL table with UTC_TIMESTAMP

I want my create table in MySQL to look like this:

CREATE TABLE Test_Tbl
(
Create_Date datetime Default UTC_TIMESTAMP()
);


However, I continually get an error. I have successfully replaced the UTC_TIMESTAMP() with each of the following and added the table.

NOW()
CURRENT_TIMESTAMP()
LOCALTIME()
LOCALTIMESTAMP()


Since I cannot get the UTC functions to work, I am thinking a setting in PHPMyAdmin may need changed? (UTC_DATE and UTC_TIME also fail.)

**

I am working in a XAMP install.

The error states "A comma or closing bracket was expected. (near "(" at position 76)"

Changing the data type to timestamp, makes no difference.

Thanks.

Answer Source

As discussed in far greater detail in the answers over at dba, this is incorrect syntax here because the function UTC_TIMESTAMP(); can't be used there. This query fails even at the command line, so upgrading or modifying phpMyAdmin won't help.

The short version of the answer is that since MySQL already stores TIMESTAMP columns as UTC times, this function wouldn't do much in this context. Normally, when you place and retrieve data, MySQL will automatically convert it back to the time zone the client is using. You may also be interested in using some other type such as DATETIME to avoid this automatic conversion.