mysql date format with changing string value
- by hacket
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?
