Bug #10466 Datatype "timestamp" displays "YYYYMMDDHHMMSS" irrespective of display sizes.
Submitted: 9 May 2005 9:58 Modified: 13 Jul 2005 19:29
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.4 Beta-standard OS:Any (any)
Assigned to: Magnus Blåudd CPU Architecture:Any

[9 May 2005 9:58] Disha
Description:
If we create a table with timestamp datatype and display size as (8), And if we try to insert the values it displays column information in following format "YYYY-MM-DD HH:MM:SS" which is incorrect according to the defined display size.

How to repeat:
Repro Steps 
 
 1. Use the database 'test' i.e. execute the following SQL statement:
	use test//

 2. Create a table 't1' ie execute the following SQL statement:
	create table t1 (f1 int, f2 timestamp(8))//

 3. Insert the values in table t1 ie execute the following SQl statement:
	Insert into t1 values (1,20050302213030)//

 4. Select the values of table 't1' ie execute the following SQl statement: 
	Select * from t1//

 5. Observe that following value is displayed:
	+------+---------------------+
	| f1      | f2                         |
	+------+---------------------+
	|    1    | 2005-03-02 21:30:30 |
	+------+---------------------+
	
 
    
 Expected Result: The 'select' statement should display the value as:

	+------+---------------------+
	| f1      | f2                         |
	+------+---------------------+
	|    1   | 20050302	                |
	+------+---------------------+
	

 Actual Result	: The 'Select' statement displays the output as follows:

	+------+---------------------+
	| f1      | f2                           |
	+------+---------------------+
	|    1    | 2005-03-02 21:30:30 |
	+------+---------------------+
[9 May 2005 11:57] Hartmut Holzgraefe
According to the documentation TIMESTAMP doesn't take a precision or display width 
spec at all and SHOW CREATE TABLE doesn't show one either so the value seems
to be ignored silently.

There should at least be a warning instead.
[15 Jun 2005 12:16] Magnus Blåudd
To get the date formatted as "20050302" use the DATE_FORMAT function.
Ex: 
mysql> SELECT DATE_FORMAT('2005-03-02 22:23:00', '%Y%m%d');
+----------------------------------------------+
| DATE_FORMAT('2005-03-02 22:23:00', '%Y%m%d') |
+----------------------------------------------+
| 20050302                                     |
+----------------------------------------------+
1 row in set (0.01 sec)
[16 Jun 2005 15:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26066
[20 Jun 2005 10:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26179
[20 Jun 2005 10:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26180
[27 Jun 2005 13:27] Magnus Blåudd
Added printout of warnings if TIMESTAMP is used with length, ex TIMESTAMP(6) produces warning "Warning 1287    'TIMESTAMP(6)' is deprecated; use 'TIMESTAMP' instead "

Pushed to 5.0.9
[13 Jul 2005 19:29] Paul Dubois
Noted in 5.0.9 changelog.