TalkPHP

TalkPHP (http://www.talkphp.com/forums.php)
-   Absolute Beginners (http://www.talkphp.com/absolute-beginners/)
-   -   Adding Timestamps in SQL? (http://www.talkphp.com/absolute-beginners/3270-adding-timestamps-sql.html)

Theo 08-22-2008 10:54 AM

Adding Timestamps in SQL?
 
Okay, this should be stupidly easy, but I can't figure it out :(

I have this table in my database:

PHP Code:

CREATE TABLE IF NOT EXISTS `articles` (
  `
idint(10unsigned NOT NULL auto_increment,
  `
release_datetimestamp NOT NULL default CURRENT_TIMESTAMP,
  `
titlevarchar(100NOT NULL,
  `
contentlongtext NOT NULL,
  `
linkvarchar(100NOT NULL,
  `
author_idint(10unsigned NOT NULL,
  
PRIMARY KEY  (`id`),
  
KEY `release_date` (`release_date`,`author_id`),
  
FULLTEXT KEY `title` (`title`,`content`)
ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=

I'm trying to set the release date when I post a new article like this (using PDO to access a MySQL database):

PHP Code:

//    Construct our timestamp.    
$theTimestamp mktime000$_POST['month'], $_POST['day'], $_POST['year'] );
$theStatement $theDatabase->prepare( <<<_SQL_
    INSERT INTO `articles` 
    ( `release_date`, `title`, `content`, `author_id` )
    VALUES ( ?, ?, ?, ? )
_SQL_
    );
            
$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?

buggabill 08-22-2008 12:28 PM

It looks like a data type issue. Take a look at the docs for MySQL's TIMESTAMP datatype.

Quote:

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.

Theo 08-23-2008 04:27 AM

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 05:05 AM.

Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0