Why a data type of Timestamp can hold a large number of characters 1

[maupiti]

2) SQL 2000 Server

Why a Data Type of "timestamp" in an SQL table that have an eight characters limit can hold The value "Aug 9 2006 4:56PM" and SQL did not flag any error ?

 

[Golom]

Because it doesn't.

"Aug 9 2006 4:56PM" is not what is stored in the timestamp field. What is actually stored there is the number 38938.70555. Timestamps are (internally) a numeric double and the "8" is the number of Bytes that it takes to store a Double.

The "Aug 9 2006 4:56PM" is just a formatted result of that numeric value.

 

[fneily]

To expand on the post of Mr. Golom

The range is '1970-01-01 00:00:00' to sometime in the year 2037. In MySQL 4.0 and earlier, TIMESTAMP values are displayed in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or missing), 12, 8, or 6, but allows you to assign values to TIMESTAMP columns using either strings or numbers. From MySQL 4.1, TIMESTAMP is returned as a string with the format 'YYYY-MM-DD HH:MM:SS'. If you want to have this as a number you should add +0 to the timestamp column.

The M argument affects only how a TIMESTAMP column is displayed; its values always are stored using 4 bytes each. Note that TIMESTAMP(M) columns where M is 8 or 14 are reported to be numbers while other TIMESTAMP(M) columns are reported to be strings. This is just to ensure that one can reliably dump and restore the table with these types!