Mysql string to date with given format containing a timezone specifier

Question

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

STR_TO_DATE(Wed Aug 13 17:51:06 GMT+05:30 2014, %a %b %d %H:%i:%s %x'); — user2800040, Aug 13 '14 at 12:48
Its not a duplicate the format is a lot different thats where i am struggling — user2800040, Aug 13 '14 at 12:53
Well, it's like asking how to add 1 and 2 and claiming that `2+3` doesn't tell you how. — Álvaro González, Aug 13 '14 at 12:54
@ÁlvaroG.Vicario Thanks for the metaphor. I couldn't even find words to explain him why and how he's wrong — Kabulan0lak, Aug 13 '14 at 12:57
Yeah not every one has English as primary language.And please don't go away from topic — user2800040, Aug 13 '14 at 13:00
'ang on. This datestamp has the timezone specifier in the middle. It's not as trivial as it looks. — O. Jones, Aug 13 '14 at 13:05
Fair enough. But please note that most relevant info was not present in the original question, thus my feedback. — Álvaro González, Aug 13 '14 at 15:22

Bohemian · Accepted Answer · 2014-08-13T14:39:58.630

2

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)

edited Aug 13 '14 at 14:39

answered Aug 13 '14 at 13:20

Bohemian

412,405
93
575
722

You didn't say you have multiple timezones. It is acceptable to ignore the timezone? – Bohemian Aug 13 '14 at 13:21
yeah we can ignore it – user2800040 Aug 13 '14 at 13:23
well frankly i didn't understand a thing. I didn't even know we can do that in sql.Can you change this query according to my query in question.Please – user2800040 Aug 13 '14 at 13:37
It works if i remove the -7 part. Do i have to add something more than -7? – user2800040 Aug 13 '14 at 14:09

Mysql string to date with given format containing a timezone specifier

1 Answers1

Linked