Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Jan 2009
    Posts
    156
    Thanks
    47
    Thanked 0 Times in 0 Posts

    Help converting varchar and int dates to something sortable..

    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?
    Attached Thumbnails Attached Thumbnails Help converting varchar and int dates to something sortable..-dates.jpg  

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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??

  • #3
    Regular Coder
    Join Date
    Jan 2009
    Posts
    156
    Thanks
    47
    Thanked 0 Times in 0 Posts
    Honestly, just fixing in php will suffice for now. I'm using sortable tables and they require the following date format:

    03/03/09

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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
    Code:
    SELECT DATE_ADD( '19700101', INTERVAL ExpDate SECOND) AS ExpDateAsDateTime ...

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Yeah, that's what I said. <grin/> And yes, he could convert it to standard DATETIME value as I showed, no?

    But...

    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.

  • #7
    Regular Coder
    Join Date
    Jan 2009
    Posts
    156
    Thanks
    47
    Thanked 0 Times in 0 Posts
    Here's the script I'm using to assist with sorting (along with the required date format):

    http://www.phatfusion.net/sortabletable/

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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?

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    1) fix the data type
    or
    2) use str_to_date at the database level


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •