CODEWITHSUNDEEP
mysqlsql
Pristine Kallio
Pristine Kallio

Reputation: 515

query to feed UNIX_TIMESTAMP as INT into another column

CREATE TABLE IF NOT EXISTS `sample_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thread` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `subject` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `content` mediumtext COLLATE utf8_unicode_ci NOT NULL,
  `date_entered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time_stamp` int(11) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=283 ;

I have a table called sample_table and in that table, I have a column where new record is saved to date_entered column as a datetime format. I want to run a query to add unix timestamp to an extra column called 'time_stamp' I've just created. I try but ended up having all the time records set to 1 date_entered and 1 time_stamp.

UPDATE sample_table SET time_stamp = UNIX_TIMESTAMP(date_entered);

I want unix timestamp of each of date_entered be featured on the time_stamp column next to the date_entered column. What should I do?

Upvotes: 0

Views: 70

Answers (1)

Pristine Kallio
Pristine Kallio

Reputation: 515

Never mind, I think I answered my own question. Here is the solution.

UPDATE sample_table SET date_entered = date_entered, time_stamp = UNIX_TIMESTAMP(date_entered)

Upvotes: 1

Related Questions