从混合字符串转换为Unix时间戳

So, if you're looking for a database solution and it's mySQL you could construct an update statement using this:

select UNIX_TIMESTAMP( STR_TO_DATE('24/05/2014 - 12:26PM', '%d/%m/%Y - %h:%i%p') );

What you'd want to do is use SUBSTR or some other function to grab the timestamp part from the column you've stored this log information in, then convert it like the above, and then use an UPDATE statement combined with that select.

Here's a full example:

create database so1;
use so1;

create table logs ( old varchar(128), new bigint(20));
INSERT INTO logs (old) VALUES ('User Admin Logged in from xx.xx.xx.xx - 24/05/2014 - 12:26PM');
INSERT INTO logs (old) VALUES ('User Admin Logged in from xx.xx.xx.xx - 24/05/2014 - 12:27PM');
INSERT INTO logs (old) VALUES ('User Admin Logged in from xx.xx.xx.xx - 24/05/2014 - 12:28PM');

update logs a left join (SELECT  UNIX_TIMESTAMP ( STR_TO_DATE( RIGHT(old, 20), '%d/%m/%Y - %h:%i%p') ) as new, old FROM logs) b on a.old=b.old   set a.new = b.new;

How it works:

I'm making the assumption that your log strings last 20 characters are always the timestamp you're trying to get. So, the SQL function RIGHT(str,n) will return the rightmost n characters of some str of data. In this case, the old column in my example.

So: RIGHT('User Admin Logged in from xx.xx.xx.xx - 24/05/2014 - 12:26PM', 20) will return 24/05/2014 - 12:26PM

Next, we need to convert that string into a time that mysql can understand. The mySQL function STR_TO_TIME is made for this. It takes two arguments: the first is the string you're going to convert (the output of our RIGHT function, and then the second argument is the format that will be parsed out. In this case, %d/%m/%Y - %h:%i%p , you can find a list of what each of those mean here but its the day/month/Year a space and a dash, then the hour colin, minutes AM|PM. So, mySQL will take your string and convert it. If you just run STR_TO_TIME + RIGHT you'll get a datetime column back, but you said you want a timestamp...

So finally we get to the last step of the conversion, which is simply ask mySQL to convert it the datetime object to a timestamp with UNIX_TIMESTAMP. And bam, there's the value you want.

Next up is the actual update, this is so you can do this all in one sweep. There exists something called an "update join" for mySQL (and most sql's I believe). The syntax is:

UPDATE table1 LEFT JOIN table2 ON .field = .field SET .somefield =

So we want to update our log table (in the example) with the results of the conversion being done to the column. So we perform a JOIN with the table and the results of our conversion. In order to join the tables and make sure things match we pull out the old column from our new results as well. That's a lot of words so in code:

UPDATE logs a 
    LEFT JOIN (...) b ON a.old=b.old 
SET a.new = b.new;

I've omitted the query to get the results so that you can more easily see the update syntax. So all we need after this point is the results themselves to join against, which using the conversion process above, and the fact that we need to have the old column to join against we combine:

SELECT UNIX_TIMESTAMP(STR_TO_DATE(RIGHT(old,20), '%d/%m/%Y - %h:%i%p')) AS new, old FROM logs

and the update query to get the final:

UPDATE logs a 
    LEFT JOIN (
         SELECT UNIX_TIMESTAMP(STR_TO_DATE(RIGHT(old,20), '%d/%m/%Y - %h:%i%p')) AS new, old FROM logs
    ) b ON a.old=b.old 
SET a.new = b.new;