PHP - MYSQL: Convert Timestamp --> Time

I have this MYSQL Table:

[ID] [TIMESTAMP] [TIME]
[1] [2010-05-29 01:17:35] []
[1] [2010-05-29 01:23:42] []

... and so on

Now I need to copy the TIMESTAMP value into the TIME rows. The new TIME rows are created via the PHP command time();

Problem: I have no idea how to do this. Maybe iwht a MySQL command, maybe via PHP? Please help me out!

My Table:

CREATE TABLE `comments` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `ip` int(10) unsigned NOT NULL,
  `name` varchar(40) NOT NULL,
  `comment` text NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `email` varchar(30) NOT NULL,
  `time` int(100) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `news_id` (`m0arid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17198 ;

Goal: I want to remove the Timestamp thing. Time is better, as I see it.

PS: If you have a general hint for me, looking at this table, please tell me :).

This question and answers originated from www.stackoverflow.com
Question by (3/2/2011 3:29:03 PM)

Answer

You can switch back and forth between dates and Unix timestamps (don't confuse with MySQL's TIMESTAMP column type) with these functions:

As about your table, well, I see little use for storing dates as integers. You cannot do any date calculation (or even display as date) without making prior conversions. Furthermore, Unix timestamps in PHP are not extremely portable: some platforms won't even allow to store dates before 1970...

Find More Answers
Related Topics  php  mysql  time  timestamp
Related Questions