2014-05-24 03:33
浏览 54


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!


    $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];

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


图片转代码服务由CSDN问答提供 功能建议

今天我正在寻找一些帮助,我正在对我的数据库进行更新。 在我们内部系统的先前版本中,我们使用以下样式字符串作为系统日志;

 用户管理员从xx.xx.xx.xx登录 -  24/05/2014  -  12:26 PM 

所有时间都按照上面的单个字符串写入数据库,但我希望系统现代化并使用时间戳,以便搜索可以 对他们做了。 数据库中有几千条记录,都带有上面的时间标记。

我正在寻找帮助打破字符串,提取时间然后将其转换为时间戳,节省 之后我到了一个数据库。


  strtotime($  string。“UTC + 10”); 

使用“now”,“today”和“June 31st”之类的术语似乎有效,但字串 '24 / 05/2014 - 12:26 PM'没有。

除了这个问题之外,我注意到日期信息之间的使用会强制使用 脚本把它读作mm / dd / yyyy而不是dd / mm / yyyy

建议的方法是什么,记住我想从数据库中提取这些,得到时间戳, 全部自动将其保存回数据库。 服务器负载不是问题,因为它只需要发生一次。


I 基于提供的答案使用以下代码来快速有效地完成此任务。 希望它能帮助其他人!

 $ 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]。'  | 设置时间戳:'。$ data [0]; 
 $ QueryCount ++; 
 // echo $ ProcessText; 
 echo'&lt; strong&gt; Total Queries Run:'。$ QueryCount。'&lt; /  strong&gt;'; 
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dqt66847 2014-05-24 03:42

    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 =;

    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 =;

    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 =;    
    打赏 评论

相关推荐 更多相似问题