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!