mysql 00/00/00 00:00:00 grab and update to a int timestamp in new column

Alright, I have a field which is called timestamp and it is formatted as such: 00/00/00 00:00:00 and I want to grab that field and then updated it in int timestamp form to a field called tm_unix. So how would I do that with a single update? I can do it as a php loop b开发者_高级运维ut thought there has to be a way to do it mysql and just need a quick answer.

Unless someone can tell me how to find less than 30 days on the format 00/00/00 00:00:00?

Thanks

Edit: I am using mysql4

---

UPDATE nameoftable SET tm_unix=UNIX_TIMESTAMP(timestamp)

---

I don't know the order of day,month and year in 00/00/00 00:00:00

I give you an example

select datediff(curdate(),str_to_date('21/03/11 00:00:00','%d/%m/%Y %T')) -- 32

Put modifier in the right order to match your situation. As you see you can calculate date differences without using unix timestamp. I suggest you to use str_to_date() function with an update query in order to modify your format.

edit. I've added a simple example:

create table example (
id int not null auto_increment primary key,
datestr varchar(20),
unixfield int) engine = myisam;

insert into example (datestr)
values 
('01/04/11 15:03:02'),
('22/04/11 19:03:02');

update example
set unixfield = unix_timestamp(str_to_date(datestr,'%d/%m/%Y %T'));

select *,from_unixtime(unixfield) from example;

+----+-------------------+------------+--------------------------+
| id | datestr           | unixfield  | from_unixtime(unixfield) |
+----+-------------------+------------+--------------------------+
|  1 | 01/04/11 15:03:02 | 1301662982 | 2011-04-01 15:03:02      |
|  2 | 22/04/11 19:03:02 | 1303491782 | 2011-04-22 19:03:02      |
+----+-------------------+------------+--------------------------+
2 rows in set (0.00 sec)

EDIT. SECOND UPDATE. This is an example of how you can emulate str_to_date() playing with substring() and substring_index() functions.

set @dtstring = '21/03/11 15:23:10';
select str_to_date(@dtstring,'%d/%m/%Y %T'); -- 2011-03-21 15:23:10
select concat('20',substring(@dtstring,7,2),'-',substring(@dtstring,4,2),'-',substring(@dtstring,1,2),' ',substring_index(@dtstring,' ',-1)) -- 2011-03-21 15:23:10

So, my update query will become:

update example
set unixfield = unix_timestamp(concat('20',substring(datestr,7,2),'-',substring(datestr,4,2),'-',substring(datestr,1,2),' ',substring_index(datestr,' ',-1)));