douyiyi5284 2014-05-24 03:33
浏览 54
已采纳

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

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

?>
  • 写回答

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 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;    
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥50 有偿求qftp工具。能连接,下载文件,发送代码,windows环境,最好qt6 要qt creator写的
  • ¥70 刚刚看到一个人的网站居然是通过cname访问的
  • ¥15 Attributeerror:super object has no attribute '__sklearn_tags__'_'
  • ¥15 逆置单链表输出不完整
  • ¥15 宇视vms-B200-A16@R启动不了,如下图所示,在软件工具搜不到,如何解决?(操作系统-linux)
  • ¥500 寻找一名电子工程师完成pcb主板设计(拒绝AI生成式答案)
  • ¥15 关于#mysql#的问题:UNION ALL(相关搜索:sql语句)
  • ¥15 matlab二位可视化能否针对不同数值范围分开分级?
  • ¥15 已经创建了模拟器但是不能用来运行app 怎么办😭自己搞两天了
  • ¥15 关于#极限编程#的问题,请各位专家解答!