mysql date format with changing string value
Posted
by
hacket
on Stack Overflow
See other posts from Stack Overflow
or by hacket
Published on 2012-08-29T15:30:45Z
Indexed on
2012/08/29
15:38 UTC
Read the original article
Hit count: 188
mysql
I have a field called Timestamp, that stores its values as text as opposed to an actual Timestamp. The logging application is unchangeable, unfortunately.
So
table.Timestamp -> text field with format -> "Wed Mar 02 13:28:59 CDT 2011"
I have been developing a query to purge all but the most recent row using this as my Timestamp selector, which is also converting the string into a date ->
MAX( STR_To_DATE( table.Timestamp , '%a %b %d %H:%i:%s CDT %Y' )
My query works perfectly...
However, what I've found is that the string value -> 'CDT' -> changes between 'CDT' and 'CST' depending on whether the current time is daylight savings time or not. During daylight savings time, it logs as 'CDT', and vice versa.
So all the rows that contain 'CST' get ignored when I run this ->
MAX( STR_To_DATE( table.Timestamp , '%a %b %d %H:%i:%s CDT %Y' )
and all the rows that contain 'CDT' get ignored when I run this ->
MAX( STR_To_DATE( table.Timestamp , '%a %b %d %H:%i:%s CST %Y' )
Is there a way to make it run against both string formats?
© Stack Overflow or respective owner