Mysql string as of today with the given format containing the timezone specifier
I have a string column in my database as
Wed Aug 13 17:51:06 GMT+05:30 2014
Can I do this until today and use this in the where clause to get the records
where
Timecolumn >( CURDATE()-7)
Note that the time zone specifier GMT+05:30
is between the time of day string and the four-digit year string.
I am using this query in phpmyadmin but I am not getting any results and yes they exist
SELECT * FROM `calldetails` WHERE STR_TO_DATE('date', '%a %b %d %H:%i:%s %x %Y')>(CURDATE()-7)
Also date is the name of my column here
+3
source to share
1 answer
Use substr()
to truncate time zone data.
Also CURDATE() - 7
should beSUBDATE(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 |
Applicable to your request:
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)
+2
source to share