MySQL Bugs: #17817: docs on localtimestamp and UTC

Bug #17817	docs on localtimestamp and UTC_TIMESTAMP not complete
Submitted:	1 Mar 2006 6:12	Modified:	20 Apr 2006 12:34
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0-BK, 5.1.x	OS:	Any (any)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
I notice the existence of localtimestamp and UTC_TIMESTAMP.  They are briefly mentioned in the docs, but I do not think the docs are complete or accurate.

By experimenting I find that localtimestamp can be used exactly like CURRENT_TIMESTAMP.  ' ...default localtimestamp on update localtimestamp' executes without erros but server transforms to CURRENT_TIMESTAMP.  UTC_TIMESTAMP seems not to be allowed as default.

I know how localtimestamp and UTC_TIMESTAMP work on Oracle.  I assume that that is where MySQL aim at too?

How to repeat:
1)

execute:
"alter table `test`.`tablename1` add column `ts` timestamp  DEFAULT localtimestamp NOT NULL  after `id`;"

'show create table' returns:

CREATE TABLE `tablename1` (                           
              `id` bigint(20) NOT NULL auto_increment,            
              `t` varchar(50) default NULL,                       
              `ts` timestamp NOT NULL default CURRENT_TIMESTAMP,  
              PRIMARY KEY (`id`)                                  
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1

execute
"alter table `test`.`tablename1` add column `ts` timestamp  DEFAULT localtimestamp on update localtimestamp NOT NULL  after `t`;"

'show create table' returns:

CREATE TABLE `tablename1` (                                                       
              `id` bigint(20) NOT NULL auto_increment,                                        
              `t` varchar(50) default NULL,                                                   
              `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  
              PRIMARY KEY (`id`)                                                              
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1

2)

execute:

"alter table `test`.`tablename1` add column `ts` timestamp  DEFAULT UTC_TIMESTAMP NOT NULL  after `t`;"

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UTC_TIMESTAMP NOT NULL  after `t`' at line 1
(0 ms taken)

2a) 

However this works:

"insert into tablename1 values (4,'5',UTC_TIMESTAMP);"

... and REALLY :-) inserts UTC_TIMESTAMP - not CURRENT_TIMESTAMP

Suggested fix:
Document it! 
And preferably make the different TIMESTAMPs behave identically with DEFAULT and 'on update'

:-)

Thank you for a problem report. Verified just as described with 5.0.19-BK also. It is, at least, a documentation request.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

LOCALTIMESTAMP is allowed because it is a synonym for
CURRENT_TIMESTAMP.

UTC_TIMESTAMP is not allowed. It is not a synonym for
CURRENT_TIMESTAMP. (It doesn't have the same range of
values as the TIMESTAMP column anyway unless the current
time zone is UTC.)

I'll add this information to the TIMESTAMP column description.

I hope the docs will provide a better explanation than this:

"LOCALTIMESTAMP is allowed because it is a synonym for
CURRENT_TIMESTAMP.

UTC_TIMESTAMP is not allowed. It is not a synonym for
CURRENT_TIMESTAMP. (It doesn't have the same range of
values as the TIMESTAMP column anyway unless the current
time zone is UTC.)"

1) Ok .. with MySQL (unlike ORACLE) localtimestamp is supposed to be synonym to CURRENT_TIMESTAMP.  What is the idea of having it then?

2) UTC_TIMESTAMP is not allowed ???????  What do you mean??  Do you mean that it is not allowed as DEFAULT.  It IS certainly allowed as a VALUE for a TIMESTAMP variable or column.  How would you else explain that I can assign such value?  If it was not allowed the server should reject it!

Could you tell me where in the online-docs i can read the new documentation?

Right, UTC_TIMESTAMP isn't allowed for DEFAULT (or for ON UPDATE).
Sorry, I wasn't clear about that. The updated doc page is here:

http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html

The relevant paragraph:

CURRENT_TIMESTAMP or any of its synonyms (CURRENT_TIMESTAMP(), 
NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, or LOCALTIMESTAMP()) 
can be used in the DEFAULT and ON UPDATE clauses. They all mean
"the current timestamp." (UTC_TIMESTAMP is not allowed. Its range 
of values does not align with those of the TIMESTAMP column anyway
unless the current time zone is UTC.)

I don't understand what your point 1) means.

explanation of 1)

The difference between this function (LOCALTIMESTAMP) and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

From:
http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/server.920/a96540/functi...

But I guess that "TIMESTAMP WITH TIME ZONE value" is not supported by MySQL??

Yes, that's correct.  There is no "TIMESTAMP WITH TIME ZONE value"
in MySQL.

:-)

Thanks for the information!  I think you can close this issue now.
Things are documented the way they are.

Just for your info: I am a betatester of 3rd party MySQL client SQLyog.
New release introduces 'smart' GUI-support for those TIMESTAMPS as well as current_user, NULL et.al KEYWORDS.  I needed precise info to verify that it does thing correctly according to MySQL specs .. and did not find the specs clear enough!

A last comment: I still don't see any logical reason why UTC_TIMESTAMP cannot be used as default and with on-update.  Consider two MySQL servers operating each their time-zone being replicated or synchronized (or clustered!!!) or just an international company operating different servers where TIME-information should not be the local time of the server but an identical time-value for easy comparison of data.  As of now you will have to do something in the client code to achieve that.  Would be much easier to update with UTC_TIMESTAMP in the column definition.

BTW: what is the correct timezone of a MySQL server operating in a synchronized orbit in outer space ?? :-)  CURRENT_TIMESTAMP does not make sense here.  UTC_TIMESTAMP always makes sense!  But also airline companies etc. operate ONE GLOBAL time internally and no LOCAL TIMES!

Have a nice day!