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

Question

Today I am looking for some help with an update I am making to my database. In the previous version of our internal system we used the following styled strings as system logs;

User Admin Logged in from xx.xx.xx.xx - 24/05/2014 - 12:26PM

All times are written to the database in a single string as above, however I am looking to modernize the system and use timestamps so that searches can be done on them. There are several thousand records in the database, all with the time marker as above.

I am looking for assistance in breaking the string, extracting the time and then converting it to a timestamp, saving it to a database thereafter.

So far I have tried to use the following manually just to see what results;

strtotime($string . " UTC+10");

Using terms such as 'now' and 'today' and 'June 31st' seem to work, however the string of '24/05/2014 - 12:26PM' does not.

In addition to that issue, it has come to my attention that the use of / between the date information will then force the script to read it as mm/dd/yyyy instead of dd/mm/yyyy

What is the advised way to proceed, remembering I want to extract these from a database, get the timestamp, save it back to the database all automatically. Server load isnt an issue as it only needs to occur once.

Thank you greatly as always :)

---

I used the following code based on the answer provided to accomplish this quickly and efficiently. Hope it helps others!

<?php
    require_once("config.inc.php");

    $ProcessText = '';
    $QueryCount = 0;
    $sql = mysql_query("SELECT UNIX_TIMESTAMP(STR_TO_DATE(RIGHT(log,20), '%d/%m/%Y - %h:%i%p')),dbid FROM systemlogs WHERE `timestamp` = '';");
    while($data = mysql_fetch_array($sql)){
        $doupdate = mysql_query("UPDATE `systemlogs` SET `timestamp` = '".$data[0]."' WHERE `dbid` = '".$data[1]."' LIMIT 1");
        $ProcessText .= 'Updated: '.$data[1].' | Set Timestamp: '.$data[0];
        $QueryCount++;
    }

    //echo $ProcessText;
    echo '<strong>Total Queries Run: '.$QueryCount.'</strong>';

?>

Answer 1

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;