query to feed UNIX_TIMESTAMP as INT into another column

Question

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?

It's not necessary to have two different columns for that. Check this: stackoverflow.com/questions/5331026/… — nanocv
– nanocv, Commented May 5, 2016 at 18:03
The query you've written should have worked. It shouldn't set them all to the same thing. — Barmar
– Barmar, Commented May 5, 2016 at 18:10
It did on my end. All were set to the same thing. I have 2 different columns because I haven't found the specific code in my PHP application to be fixed to perform groceryCRUD with datatable theme correctly. — Pristine Kallio
– Pristine Kallio, Commented May 5, 2016 at 18:23
I just tried it and your original query worked: sqlfiddle.com/#!9/93ac64/1 — Barmar
– Barmar, Commented May 5, 2016 at 19:36

Pristine Kallio · Accepted Answer · 2016-05-05 18:05:23Z

1

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)

answered May 5, 2016 at 18:05

Pristine Kallio

5156 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Barmar Over a year ago

There's no need for date_entered = date_entered.

Pristine Kallio Over a year ago

Bamar, thank you for your reply. However, with my initial query, I got all every rows with the same set of: 2016-05-05 18:00:20 || 1462471220

Barmar Over a year ago

I see the problem, it's the ON UPDATE CURRENT_TIMESTAMP. When you update the time_stamp column, that causes the date_entered column to be reset to the current time.

Pristine Kallio Over a year ago

How to fix? Like this would resolve it? date_entered timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP CURRENT_TIMESTAMP,

Barmar Over a year ago

It looks like your answer solves it. Assigning back to date_entered overrides the ON UPDATE clause.

|

Collectives™ on Stack Overflow

query to feed UNIX_TIMESTAMP as INT into another column

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related