Jump to content


Photo

20/20 hindsight - date problem


  • Please log in to reply
4 replies to this topic

#1 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 06 September 2006 - 11:06 PM

A problem with legacy data (lots of it, unfortunately) in a MySQL database that needs to be sorted by date except that the 'date' field is actually just a varchar field generated thus:

$dated = gmdate("h:i a"). "[GMT] on ". gmdate("F d Y")

I'm hoping that the query can be constructed with 'magic' that someone can suggest will work with that date data and php4.4.2/MySQL 4.1.21-standard
Legend has it that reading the manual never killed anyone.
My site

#2 MarioRossi

MarioRossi
  • Members
  • PipPip
  • Member
  • 25 posts

Posted 06 September 2006 - 11:18 PM

tried the strtotime function on it? If its all in the same format you should be OK, just add another db field with a more universal format and rewrite back to the DB

http://uk2.php.net/strtotime

#3 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 06 September 2006 - 11:30 PM

By way of clarification, an example of the 'date' field is 12:05 pm [GMT] on September 06 2006.

I don't want to re-write the 'real' date back to the database, I *only* want an SQL query that can sort the data by yyyy-mm-dd when the database field contains a string like the example above.
Legend has it that reading the manual never killed anyone.
My site

#4 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 07 September 2006 - 01:11 AM

SELECT
*
FROM
tablename
ORDER BY
STR_TO_DATE(SUBSTRING(date_column, LOCATE('on', date_column) + 3), '%M %d %Y')

You should be able to set the substring start position to a hardcoded 19 but I'm not sure.
STR_TO_DATE(SUBSTRING(date_column, 19), '%M %d %Y')

It may be faster to sort this in PHP with usort.

#5 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 07 September 2006 - 01:44 AM

The posted query did everything I had hoped for. Thanks, shoz.
Legend has it that reading the manual never killed anyone.
My site




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users