View Full Version : Help converting varchar and int dates to something sortable..

03-03-2009, 07:22 AM
Here's what's currently in the code:

$purchased = $a1['Date'];

$expires = date('M d, Y', $a1['ExpDate']);

Attached is a screenshot of Date and ExpDate in phpMyAdmin. How can these be converted in php so that I can display them both as m/d/Y?

Old Pedant
03-03-2009, 08:19 AM
Which do you want? Sortable or displayable???

Seems to me that the DATE field is already displayable. After all, phpMyAdmin is written in PHP and it can display it.

It was clearly an error to have stored a date in a VARCHAR field, but if you don't care about sortable/orderable, it looks like it works.

I don't pretend to understand what that INT value you show is supposed to mean. Is that supposed to be number of seconds or milliseconds since 1/1/1970, the Linux/Unix standard measure???

Anyway, do you want to fix the DB, change both of those to proper DATETIME fields, as they should be, or do you really just want to fix the display in PHP??

03-03-2009, 08:43 AM
Honestly, just fixing in php will suffice for now. I'm using sortable tables and they require the following date format:


Old Pedant
03-03-2009, 09:02 AM
Can't help you then. Not a PHP programmer. Probably need to ask in PHP forum.

I did check, and indeed that integer you are showing is the number of seconds since 1/1/1970 0:00:00, the standard Linux/Unix "timestamp" value.

That would be reasonably easy to convert to a standard MySQL DateTime, if you care.

Probably something like

SELECT DATE_ADD( '19700101', INTERVAL ExpDate SECOND) AS ExpDateAsDateTime ...

03-03-2009, 04:21 PM
use DATE data type for your date fields and store them in yyyymmdd format, then use DATE_FORMAT to display them in any other way you want.

Old Pedant, I'm guessing in the INT field it is UNIXTIME.

Old Pedant
03-03-2009, 09:29 PM
Yeah, that's what I said. <grin/> And yes, he could convert it to standard DATETIME value as I showed, no?


But I get the sense that he is unable to reorganize/rebuild the DB and is looking for an "ad hoc" solution to converting these ugly values to standard DATETIME. Except then he says he wants it as "03/03/09" for PHP to do sorting. (Which has to be the worst kind of way to format a date for sorting, but that's another topic.) So I dunno.

03-03-2009, 10:05 PM
Here's the script I'm using to assist with sorting (along with the required date format):


Old Pedant
03-04-2009, 03:53 AM
I thought you said you were using PHP to do the sorting!

...fixing in php will suffice for now. I'm using sortable tables and they require...
Hmmm...nope, you didn't really say that.

So it's really JS doing the sorting. Okay, then I don't care if the sort is ugly, becuz client computers always have billions of spare CPU cycles to burn. <grin/>

So it's really a question of do you want to use MySQL to convert the funky data to that format or PHP. And if you can do it in PHP, then go for it. As Guelph said, the integer number is a UnixTime value and as tightly tied to Linux/Unix as PHP is one would assume there are functions in PHP to convert back and forth. The funky "Mar 3, 09" is just a matter of string manipulation: Look up the month abbreviation (e.g., "Mar") in a table that equates it to 3 and the rest is just shoving characters around in PHP strings, no?

03-04-2009, 06:43 AM
1) fix the data type
2) use str_to_date at the database level