Adding Timestamps in SQL?
Okay, this should be stupidly easy, but I can't figure it out :(
I have this table in my database:
CREATE TABLE IF NOT EXISTS `articles` (
`id` int(10) unsigned NOT NULL auto_increment,
`release_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`title` varchar(100) NOT NULL,
`content` longtext NOT NULL,
`link` varchar(100) NOT NULL,
`author_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `release_date` (`release_date`,`author_id`),
FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
I'm trying to set the release date when I post a new article like this (using PDO to access a MySQL database):
// Construct our timestamp.
$theTimestamp = mktime( 0, 0, 0, $_POST['month'], $_POST['day'], $_POST['year'] );
$theStatement = $theDatabase->prepare( <<<_SQL_
INSERT INTO `articles`
( `release_date`, `title`, `content`, `author_id` )
VALUES ( ?, ?, ?, ? )
$theStatement->execute( array($theTimestamp, $_POST['title'], $_POST['content'], $theSession['user_id'] ) );
Whenever I run this piece of code the timestamp ends up being stored in my table as 00000000000... (i.e. Wed 31st Dec 1969 ).
I have a section of code that previews the article and constructs the timestamp in exactly the same way and that works fine. Shouldn't this just work? What am I missing here?
||08-22-2008 12:28 PM
It looks like a data type issue. Take a look at the docs for MySQL's TIMESTAMP
TIMESTAMP columns are displayed in the same format as DATETIME columns. In other words, the display width is fixed at 19 characters, and the format is 'YYYY-MM-DD HH:MM:SS'
I typically store unix timestamps as an INT.
Thanks, I've changed the data type of 'release_date' and it's all working as designed now :)
I was assuming the TIMESTAMP data type meant epoch timestamp but apparently not...
|All times are GMT. The time now is 04:00 AM.
Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0