Tags related to tag DateTime
CURDATE(1), DATE_ADD(1), DAYOFWEEK(1), MySQL(2), MySQL IF(1), Next Monday(1), NOW()(1), Previous Monday(1), timestamp(1), Web Development(1)
Finding "Next Monday" using MySQL Dates Tue, Dec 1. 2009
Several people who read my article on Exploring Mysql CURDATE and NOW. The same but different posed questions regarding how to return a valid MySQL date equivalent to "Next Monday" given any particular day, as determined by MySQL's CURDATE(). This is a little bit tricky, but can be done entirely in MySQL syntax, making it usable with Calendar applications built on top of MySQL queries, without the need to use serverside date functions. This builds upon concepts discussed in my prior article, so if you have trouble understanding the implications of using CURDATE or DATE_ADD, you should probably take a minute and read that article.
Continue reading "Finding "Next Monday" using MySQL Dates" »
Posted by David Rolston
in Web Tech Comments: (0)
Trackbacks: (0)
Defined tags for this entry: CURDATE, DateTime, DATE_ADD, DAYOFWEEK, MySQL, MySQL IF, Next Monday, Previous Monday
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 2 entries)
next page »
