0
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?

Pristine Kallio
  • 505
  • 5
  • 19
  • It's not necessary to have two different columns for that. Check this: http://stackoverflow.com/questions/5331026/is-it-possible-to-create-a-column-with-a-unix-timestamp-default-in-mysql – nanocv May 05 '16 at 18:03
  • The query you've written should have worked. It shouldn't set them all to the same thing. – Barmar May 05 '16 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 May 05 '16 at 18:23
  • I just tried it and your original query worked: http://www.sqlfiddle.com/#!9/93ac64/1 – Barmar May 05 '16 at 19:36

1 Answers1

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)
Pristine Kallio
  • 505
  • 5
  • 19
  • 1
    There's no need for `date_entered = date_entered`. – Barmar May 05 '16 at 18:08
  • 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 – Pristine Kallio May 05 '16 at 18:24
  • 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. – Barmar May 05 '16 at 19:38
  • How to fix? Like this would resolve it? `date_entered` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP CURRENT_TIMESTAMP, – Pristine Kallio May 06 '16 at 00:15
  • It looks like your answer solves it. Assigning back to `date_entered` overrides the `ON UPDATE` clause. – Barmar May 06 '16 at 00:20
  • What is that column supposed to be? The time the record was created, or the time the last change was made to the record? If it's only supposed to be the creation time, you don't need `ON UPDATE`. – Barmar May 06 '16 at 00:21
  • my PHP application is a CRUD system employing CodeIgniter framework and groceryCRUD with datatable theme. I use the application as a library of my resources, codes and references I encounter in my software development career. So, I make new entry when I find some interesting stuffs in AngularJS, NodeJS, MongoDB,... and later append with new related stuffs. I need datetime to be automatically updated. Currently, I am observing both 2 columns date_entered and time_stamp to see if the ASC & DESC sorts work correctly or need fixes. – Pristine Kallio May 06 '16 at 20:45
  • Well, you could make the applications that append stuff do `SET date_entered = now()` to update it, instead of using `ON UPDATE CURRENT_TIMESTAMP`. Or you can use your current schema and use the query in your answer to update `time_stamp` without affecting `date_entered`. – Barmar May 06 '16 at 21:06