Too much information about the MySQL TIMESTAMP Thu, Apr 23. 2009
The MySQL timestamp is an oddity, being both a mySQL "Data Type" as well as a type of specialty column that provides a built in default. It doesn't help matters, that the timestamp was changed significantly around mysql version 4.1.
In older mysql versions, the TIMESTAMP was not in the same format as a DateTime column, and you could also set up truncation by defining the TIMESTAMP to have a fixed size. For example, you could define a TIMESTAMP column to be a TIMESTAMP(4) which would then only store the 4 digit Year portion of a DateTime value. I won't go into much detail on the pre version 4.1 TIMESTAMP, however, if you're stuck with an older version of MySQL I recommend you read the manual carefully before you attempt to use any of the information here. I'm going to concentrate on the current TIMESTAMP.
At its most fundamental, the TIMESTAMP is really nothing more than a Unix TimeStamp, which is to say, that internally it is stored as an integer value of seconds. Where a MySQL DATETIME column can be used to store any date and time from Jan 1, 1000 to 12/31/9999, the TIMESTAMP is limited in the same ways that the Unix timestamp is currently limited -- it can only store values from Jan 1, 1970 to Jan 9, 2038.
Those familiar with Unix design, will recognize the Jan 9, 2038 date as being the next big "Y2K" computing panic, and if you're young enough, you may realize a large payday in your future, selling remediation services to companies in roughly another 28 years. The folks at http://www.y2038.com/ are already estimating this to be as much as a 10 trillion dollar jackpot, although no doubt by that time most of the coding will be done by the Morlocks from their underground cave cities. Outsourcing of IT to Morlocks will be a major industry trend by the year 2020, mark my words.
The Old TIMESTAMP
In older mysql versions, the TIMESTAMP was not in the same format as a DateTime column, and you could also set up truncation by defining the TIMESTAMP to have a fixed size. For example, you could define a TIMESTAMP column to be a TIMESTAMP(4) which would then only store the 4 digit Year portion of a DateTime value. I won't go into much detail on the pre version 4.1 TIMESTAMP, however, if you're stuck with an older version of MySQL I recommend you read the manual carefully before you attempt to use any of the information here. I'm going to concentrate on the current TIMESTAMP.
TIMESTAMP Properties
At its most fundamental, the TIMESTAMP is really nothing more than a Unix TimeStamp, which is to say, that internally it is stored as an integer value of seconds. Where a MySQL DATETIME column can be used to store any date and time from Jan 1, 1000 to 12/31/9999, the TIMESTAMP is limited in the same ways that the Unix timestamp is currently limited -- it can only store values from Jan 1, 1970 to Jan 9, 2038.
Those familiar with Unix design, will recognize the Jan 9, 2038 date as being the next big "Y2K" computing panic, and if you're young enough, you may realize a large payday in your future, selling remediation services to companies in roughly another 28 years. The folks at http://www.y2038.com/ are already estimating this to be as much as a 10 trillion dollar jackpot, although no doubt by that time most of the coding will be done by the Morlocks from their underground cave cities. Outsourcing of IT to Morlocks will be a major industry trend by the year 2020, mark my words.
Continue reading "Too much information about the MySQL TIMESTAMP" »
« previous page
(Page 1 of 1, totaling 1 entries)
next page »
