问题描述：

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!

图片转代码服务由CSDN问答提供

感谢您的意见，我们尽快改进~

功能建议

我对MySQL内部的时间字段有一个奇怪的问题。</ p>

在数据库中 我们有2个字段： date </ code>和 time </ code>。 数据如下所示：</ p>

  + ----------------------- + 
| 日期| 时间| 
+ ----------------------- + 
| 27/04/2017 |  11:30 | 
+ ----------------------- + 
| 01/05/2017 |  20u | 
+ ----------------------- + 
| 02/05/2017 |  20u30 | 
+ ----------------------- + 
| 03/05/2017 |  21h | 
+ ----------------------- + 
</ code> </ pre> 
这些数据是什么意思？ 好吧：</ p> 
  
 20u是20:00 </ li> 
 20u30是20:30 </ li> 
 21h是21：  00 </ li> 
</ ul> 
主要问题是我必须保持所有这些数据不受影响，并且必须找到正确显示数据的解决方案，并从此抓取时间戳或正确日期或 匹配正确的日期。</ p> 
为了了解我的需要，Query的一些旧部分使用的内容如下所示：</ p> 
  UNIX_TIMESTAMP  （CONCAT（DATE_FORMAT（STR_TO_DATE（\'date`，\'％d /％m /％Y\'），\'％Y-％m-％d\'），\'\'，`time`））
</ code> <  / pre> 
但是在匹配正常情况时失败了。</ p> 
我编写了一个PHP函数，用于在代码的某些部分显示正确的时间，如下所示：  / p> 
  function bo_time（$ string）
{
if（preg_match（“/（\\ d {1,2}）（u | h）/ Ui”，$ string  ，$ match））
{
$ string = sprintf（“％s：％s”，sprintf（\'％02d\'，$ match [1]），\'00\'）; 
} 
否则if（  preg_match（“/（\\ d {1,2}）（u | h | \\：）（\\ d {2}）/ Ui”，$ string，$ match））
{
$ string = sprintf（“  ％s：％s“，sprintf（\'％02d\'，$ 匹配[1]），（isset（$ match [3]）？  sprintf（\'％02d\'，$ match [3]）：\'00\'））; 
} 
返回$ string; 
} 
</ code> </ pre> 
使用PHP函数系统的某些部分工作正常但在系统的某些部分我按日期/时间在表之间进行匹配，一切都失败并分解。</ p> 
我的主要问题是：<  / p> 
有没有办法编写一些正则表达式来转换MySQL查询中的时间，就像我在PHP中一样？</ p> 
谢谢！</ p> 
<  / DIV> 
网友观点：
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:
Demo here:
Rextester
    
There is no regex function out of the box for MySQL.
If you must leave all existing data untouched, can you create a new field with a proper datetime format and start using that instead?
You can continue to use your PHP function to transform the data and seeing as it fails in some cases you may want to fix those issues first.
I was able to do what your PHP function does in less lines with:
// $time = 20u30
preg_replace([\'/[uh]/i\',\'/:(?![0-9]+)/\'], [\':\',\':00\'], $time);
    
Firstly, it seems completely unreasonable to leave this representation in the DB. It seems that there is a canonical representation for the time column (e.g. 20:11) and that you could adjust each field to only have that representation. If you did that, you wouldn\'t have to create hacks to work around this representation nonsense.
Now, concerning the steps for a workaround that you asked for:
Firstly, it seems that if you leave off the minutes, they are implicitly \\\"00\\\", so \\\"20u\\\" is \\\"20u00\\\". Now, if you just add two zeroes and then truncate after the fifth character, you will end up with a normalized tuple. E.g. \\\"20u\\\" -> \\\"20u00\\\" -> \\\"20u00\\\" or \\\"20:00\\\" -> \\\"20:0000\\\" -> \\\"20:00\\\".
Secondly, it seems that the separator between the hours and minutes is either a colon or the Latin small letter u, so \\\"20:15\\\" is \\\"20u15\\\". That means that if you just erase this letter (concat the first, second, fourth and fifth letter), you would get a canonical representation. E.g. \\\"20:30\\\" -> \\\"2030\\\" or \\\"20u30\\\" -> \\\"2030\\\".
As additional advantage, this doesn\'t even try to use regular expressions, which are expensive and error-prone. However, it relies on the column content being exactly what you wrote without exceptions.
    
MySQL模糊查询用法大全（正则、通配符、内置函数等）
文章来源：MySQL模糊查询用法大全（正则、通配符、内置函数等）
原文作者：陈哈哈
来源平台：CSDN
SELECT * from table where username like '%陈哈哈%' and hobby like '%牛逼'
这是一条我们在MySQL中常用到的模糊查询方法，通过通配符%来进行匹配，其实，这只是冰山一角，在MySQL中，支持模糊匹配的方法有很多，且各有各的优点。好了，今天让我带大家一起掀起MySQL的小裙子，看一看模糊查询下面还藏着多少鲜为人知的好东西。
一、MySQL通配符模糊查询(%,_)
1-1. 通配符的分类
"%" 百分号通配符: 表示任何字符出现任意次数 (可以是0次)。
"_" 下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符。当然，也可以like "陈____"，数量不限。
like操作符:LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较；但如果like后面没出现通配符，则在SQL执行优化时将 like 默认为 “=”执行
意: 如果在使用like操作符时，后面没有使用通用匹配符（%或_），那么效果是和“=”一致的。在SQL执行优化时查询优化器将 like 默认为 “=”执行,SELECT * FROM movies WHERE movie_name like '唐伯虎';只能匹配movie_name=“唐伯虎”的结果,而不能匹配像“唐伯虎点秋香”或“唐伯虎点香烟”这样的结果.
1-2. 通配符的使用
1) % 通配符:
-- 模糊匹配含有“网”字的数据