CODEWITHSUNDEEP
mysqlsql
user2800040
user2800040

Reputation: 143

Mysql string to date with given format containing a timezone specifier

I have a string column in my database as

Wed Aug 13 17:51:06 GMT+05:30 2014

Can i cast this to date and use this in where clause to get records 

where
Timecolumn >( CURDATE()-7)

Notice that the timezone specifier GMT+05:30 comes between the time-of-day string and the four digit year string.

I am running this query in phpmyadmin but i get no results and yes they do exist

 SELECT * FROM `calldetails` WHERE STR_TO_DATE('date', '%a %b %d %H:%i:%s %x %Y')>(CURDATE()-7)

Also date is name of my column here

Upvotes: 4

Views: 1761

Answers (1)

Bohemian
Bohemian

Reputation: 424953

Use substr() to excise the timezone data.
Also CURDATE() - 7 should be SUBDATE(CURDATE(), 7

select
    concat(substr(str, 1, 19),substr(str, 30)) as str_sans_tz,
    str_to_date(concat(substr(str, 1, 19),substr(str, 30)),
      '%a %b %e %H:%i:%s %Y') date
from mytable

Output (pre and post parsing):

|              STR_SANS_TZ |                          DATE |
|--------------------------|-------------------------------|
| Wed Aug 13 17:51:06 2014 | August, 13 2014 17:51:06+0000 |

SQLFiddle

Applied to your query:

SELECT * FROM `calldetails`
WHERE str_to_date(concat(substr(`date`, 1, 19), substr(`date`, 30)), '%a %b %e %H:%i:%s %Y') > SUBDATE(CURDATE(), 7)

Upvotes: 2

Related Questions