CODEWITHSUNDEEP
mysql
Jury A
Jury A

Reputation: 20082

How to extract the date from a string

I have saved a date as a string, Ex: Fri Apr 04 15:58:43 BST 2014 The year is always at the end, I want to make query to get the records who has year less than a specific year, say 2012. Is this possible? How? and is it possible to fix this issue from mysql? i.e, transform the date from string to date ?

Upvotes: 2

Views: 121

Answers (2)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

Here is a baseline query

SELECT REVERSE(LEFT(REVERSE('Fri Apr 04 15:58:43 BST 2014'),4));

Here it is executed

mysql> SELECT REVERSE(LEFT(REVERSE('Fri Apr 04 15:58:43 BST 2014'),4));
+----------------------------------------------------------+
| REVERSE(LEFT(REVERSE('Fri Apr 04 15:58:43 BST 2014'),4)) |
+----------------------------------------------------------+
| 2014                                                     |
+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

OMG What was I thinking ??? I was actually decoding it

SET @datestring = 'Fri Apr 04 15:58:43 BST 2014';
SELECT CONCAT(YYYY,'-',MM,'-',DD,' ',HHMMSS) dt FROM
(SELECT REVERSE(LEFT(REVERSE(@datestring),4)) as YYYY,
SUBSTR(FLOOR((LOCATE(LEFT(SUBSTR(@datestring,5),3),'JanFebMarAprMayJunJulAugSepOctNovDec')+2)/3)+100,2) MM,
SUBSTR(@datestring,9,2) DD,
SUBSTR(@datestring,12,8) HHMMSS) A;

and my decoding works

mysql> SET @datestring = 'Fri Apr 04 15:58:43 BST 2014';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(YYYY,'-',MM,'-',DD,' ',HHMMSS) dt FROM
    -> (SELECT REVERSE(LEFT(REVERSE(@datestring),4)) as YYYY,
    -> SUBSTR(FLOOR((LOCATE(LEFT(SUBSTR(@datestring,5),3),'JanFebMarAprMayJunJulAugSepOctNovDec')+2)/3)+100,2) MM,
    -> SUBSTR(@datestring,9,2) DD,
    -> SUBSTR(@datestring,12,8) HHMMSS) A
    -> ;
+---------------------+
| dt                  |
+---------------------+
| 2014-04-04 15:58:43 |
+---------------------+
1 row in set (0.00 sec)

mysql>

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65577

You can use the MySQL STR_TO_DATE() function to convert those values into MySQL dates:

mysql> select str_to_date('Fri Apr 04 15:58:43 BST 2014','%a %b %d %H:%i:%S BST %Y');
+------------------------------------------------------------------------+
| str_to_date('Fri Apr 04 15:58:43 BST 2014','%a %b %d %H:%i:%S BST %Y') |
+------------------------------------------------------------------------+
| 2014-04-04 15:58:43                                                    |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

Here's a rough example using your sample string to illustrate a query to return all rows with dates before 2012. It will require a full table scan, so it will not be fast, but it illustrates the basic concept of what you need to do:

select *
from your_table
where str_to_date(your_column,'%a %b %d %H:%i:%S BST %Y') < '2012-01-01'

Upvotes: 4

Related Questions