MySql-How to parse date which is attached with another string

Question

I have a database named as DB in which there is a table named as log. log table have following structure.

 +----------------+--------------+------+-----+---------+----------------+
 | Field          | Type         | Null | Key | Default | Extra          |
 +----------------+--------------+------+-----+---------+----------------+
 | id             | bigint(64)   | NO   | PRI | NULL    | auto_increment |
 | BCReq          | varchar(400) | YES  |     | NULL    |                |
 | BCRes          | varchar(400) | YES  |     | NULL    |                |
 | ServerReq      | varchar(400) | YES  |     | NULL    |                |
 | ServerRes      | varchar(400) | YES  |     | NULL    |                |
 +----------------+--------------+------+-----+---------+----------------+

and the value of BCReq field of one of the record is look like this:

 uId-->xxxxxx/regiodId-->yyy/videoTitle-->bcdes 
 /location-->Asia Pacific/BCreqtime-->24-07-2014
 10:30:16/Exception-->null

Now i want to fetch the data from log table on the basis of date .is it possible? if yes then how? Please comment if any more data required.Thanks in advance.

I'd like to point out that storing multiple values in one column is very bad practice. — Ende Neu, Aug 13 '14 at 09:34
You could use a regular expression to extract a string of a specific format... but this should be done BEFORE it is inserted into the table... The data (as others have said needs to be normalised/broken down into fields) — craig1231, Aug 13 '14 at 09:35
Agree with all the comments above. Had a rant about this a few days ago too :) http://stackoverflow.com/questions/25236652/how-to-store-multiple-values-in-same-sql-column/25236702#25236702 — rurouni88, Aug 13 '14 at 09:39
Assuming the datetime is always between `BCreqtime-->` and `/` http://sqlfiddle.com/#!2/21cd46/12 You can use that in a WHERE clause. — Mihai, Aug 13 '14 at 09:42
Thanks to everyone for quick response .I know it is very bad structure but this is existing database and there is a lot of real data .so now how i should proceed?. — Johnson, Aug 13 '14 at 09:51

rurouni88 · Accepted Answer · 2014-08-14T02:28:32.090

However, despite my dislike for the way the data has been modeled. As developers, we don't sell code...we offer solutions :P

Requirement: Fetch the data from log table on the basis of date

SQL LIKE statement it. DBAs will probably want to kill me for suggesting this, because you're perform a LIKE search on a what is probably a non-indexed column. For example

   SELECT * FROM log WHERE BCReq LIKE '%24-07-2014%'

But it will give you all records of log on that day, assuming you want to fetch it by day. I'm sure you can extend it to search by hour, minute, second or even month.

Also, a DBA behind me just blanched at what I posted :P So here's me trying to redeem myself in his eyes.

Perform your query using a non-transactional connection, so you don't lock the table for other people.

But all in all, the data model needs to be reviewed and addressed to cater for this.

Additional information to address your search between 2 dates issue: If you wish to get data within 2 dates, due to your data model, in that the field BCReq is difficult to search, a viable solution is to have a developer write up an application or script to grab that data.

A sample solution (pseudo code) could be:

Grab 2 dates from user. Let's call them start_date && end_date
Format those to dates to DD-MM-YYYY format.
Determine the days_between those start_date and end_date
For every day between start_date and end_date Run SQL query above to retrieve data Store data retrieved from each iteration to output
Do something with your output

Also the title of this question is misleading "MySql-How to parse date which is attached with another string" But the requirements were "Now i want to fetch the data from log table on the basis of date" — rurouni88, Aug 13 '14 at 09:53
Thanks but can we compare date?that means if we want fetch data between two date then how we should proceed? — Johnson, Aug 13 '14 at 10:16

MySql-How to parse date which is attached with another string

1 Answers1