duanliao6077 2017-11-05 09:18
浏览 281
已采纳

MySQL:使用查询中的特殊正则表达式规则修复时间格式

I have one wierd issue regarding time field inside MySQL.

In database we have 2 fields: date and time. Data looks like this:

+-----------------------+
|   date   |    time    |
+-----------------------+
|27/04/2017|    11:30   |
+-----------------------+
|01/05/2017|    20u     |
+-----------------------+
|02/05/2017|    20u30   |
+-----------------------+
|03/05/2017|    21h     |
+-----------------------+

What this data mean? Well:

  • 20u is 20:00
  • 20u30 is 20:30
  • 21h is 21:00

Main problem is that I MUST keep all this data untouched for some reasons and must find solution to display data properly and from this grab unix timestamp or proper date or match proper dates.

For you to understand what I need, some old part of Query what is used look like this:

UNIX_TIMESTAMP(CONCAT(DATE_FORMAT(STR_TO_DATE(`date`,'%d/%m/%Y'),'%Y-%m-%d'),' ',`time`))

But that fail inside matching what is normal.

I wrote one PHP function for displaying proper time on some parts of code and looks like this:

function bo_time($string)
{   
    if(preg_match("/(\d{1,2})(u|h)/Ui",$string, $match))
    {
        $string = sprintf("%s:%s", sprintf('%02d', $match[1]), '00' );
    }
    else if(preg_match("/(\d{1,2})(u|h|\:)(\d{2})/Ui",$string, $match))
    {
        $string = sprintf("%s:%s", sprintf('%02d', $match[1]), ( isset($match[3]) ? sprintf('%02d', $match[3]) : '00' ));
    }

    return $string;
}

With that PHP function some parts of system works fine but in some parts of system I have matching between tables by date/time and everything fail and break apart.

My main question is:

Is there a way to wrote some regex for converting time inside MySQL query like I do inside PHP?

Thanks!

  • 写回答

3条回答 默认 最新

  • duannan4486 2017-11-05 14:46
    关注

    I have created a query which seems to work, at least for the data you provided us. It only required some logic to handle the various edge cases with your data. I transform your time data into actual timestamp time strings, then concatenate with the date, also converted into an ISO format. With a proper date and time in hand, we can then call UNIX_TIMESTAMP on this timestamp string to get the data you want.

    SELECT
        date, time, UNIX_TIMESTAMP(CONCAT(date, ' ', time)) AS ts
    FROM
    (
        SELECT
            CONCAT(RIGHT(date, 4), '-', SUBSTRING(date, 4, 2), '-',
                   LEFT(date, 2)) AS date,
            CASE WHEN time REGEXP '.*[uh]$'
                 THEN CONCAT(LEFT(time, CHAR_LENGTH(time) - 1), ':00:00')
                 ELSE CONCAT(REPLACE(REPLACE(time, 'h', ':'),
                             'u', ':'), ':00') END AS time
        FROM yourTable
    ) t;
    

    Output:

    enter image description here

    Demo here:

    Rextester

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 求chat4.0解答一道线性规划题,用lingo编程运行,第一问要求写出数学模型和lingo语言编程模型,第二问第三问解答就行,我的ddl要到了谁来求了
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果