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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Regular Coder
    Join Date
    Feb 2005
    Posts
    663
    Thanks
    5
    Thanked 14 Times in 14 Posts

    time format help

    I have datetime field in a table that stores the information as. I understand how to query the field.

    And if I echo the variable $time, I will get

    2007-03-13 07:27:00

    But I'd like to echo that into anther format, perhaps

    MM/DD : HH/MM

    How would I convert the $time variable to echo into a different format?

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,024
    Thanks
    2
    Thanked 314 Times in 306 Posts
    Use the mysql date_format() function in your query. Information on the date_format() function and some examples are at this link - http://dev.mysql.com/doc/refman/5.0/...functions.html
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Regular Coder
    Join Date
    Feb 2005
    Posts
    663
    Thanks
    5
    Thanked 14 Times in 14 Posts
    I would have thought there be an easier way than having to manipulate the sql queries.

    I was hoping to extract the date and time out of the table, and then be able to manipulate the info into something like:
    PHP Code:
    echo $time;
    $time date("m/d/y"$time);
    echo 
    '<br />';
    echo 
    $time
    Unfortunately, in that example, I get 12/31/69 instead of the 3/13/07

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,024
    Thanks
    2
    Thanked 314 Times in 306 Posts
    The PHP date(...) function requires a unix timestamp. Your starting date format, 2007-03-13 07:27:00, is not a unix timestamp. It requires multiple statements in PHP to accomplish what you can do within a single statement in the query. I method I mentioned is the most efficient way of doing this.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    Regular Coder
    Join Date
    Feb 2005
    Posts
    663
    Thanks
    5
    Thanked 14 Times in 14 Posts
    Gotcha.

    Thanks

    I may just look to convert the dates to unix the db, as I think that may be the best approach for the long term.

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    That's taking a big step backwards in my opinion, it's much better to store a date or time or date/time in the database using the data types provided by the database. It's cake to format a datetime field to whatever you want, it's a pain in the arse to format an integer.

  • #7
    Regular Coder
    Join Date
    Feb 2005
    Posts
    663
    Thanks
    5
    Thanked 14 Times in 14 Posts
    Really?

    I would have thought simply using the php date function to convert the unix time into any format would be the easiest.

    So I can easily query out date and time of: 2007-03-13 07:27:00

    and turn that into a simply MM/DD : HH/Min format?

    Can you have one query, and than place it into multiple formats?

  • #8
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Using datetime data types gives you more than just ease of formatting-- you can perform comparisons and date operations (add 1 day, 5 weeks, 3 months, etc) all within a query. There really is no compelling reason not to use datetime data types.

    You can query multiple formats like this:
    PHP Code:
    $query "SELECT
    date_format(date_field, '%m/%d : %l/%i') as format1,
    date_format(date_field, '%M %D, %Y in the year of our lord, at %r) as format2
    FROM my_table"

    Read up on date/time functions in the MySQL manual here.

  • #9
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,024
    Thanks
    2
    Thanked 314 Times in 306 Posts
    There seems to be an echo in here...
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #10
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    The PHP date(...) function requires a unix timestamp. Your starting date format, 2007-03-13 07:27:00, is not a unix timestamp. It requires multiple statements in PHP to accomplish what you can do within a single statement in the query. I method I mentioned is the most efficient way of doing this.
    I disagree, I believe it's mostly preference.

    It doesn't take multiple statements.

    PHP Code:
    $result query("SELECT * FROM table");

    while (
    $row get_row($result)) {
        echo 
    "date=" date("Y/m/d h:i"$row['date']);

    You either place the burden on SQL, or on PHP. I prefer PHP, as a database system's purpose is to store data. Not doing fancy things with it. This can be neglectable often, but sometimes when you have a high traffic database. Would you want to lose CPU time on something as silly as formatting a string?

    You don't have to worry about formatting issues either when inserting into the database. Doing date math is easy (need to subtract a day? subtract 86400). PHP provides all the functions you need to construct dates from a unix timestamp.

    There are pros and cons to both methods. In the end, it is whichever you are comfortable with.

  • #11
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,024
    Thanks
    2
    Thanked 314 Times in 306 Posts
    His existing database column is a datetime - 2007-03-13 07:27:00. The PHP date(...) function won't work in this case, which is why the code in the first (Edit: third) post, using date(...) does not produce the correct results...
    Last edited by CFMaBiSmAd; 03-14-2007 at 03:56 PM.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #12
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    But when the user is considering switching to a unix timestamp, it doesn't matter what is there currently.

    Some situations benefit from switching to another method.

  • #13
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,024
    Thanks
    2
    Thanked 314 Times in 306 Posts
    Re-read this -
    Quote Originally Posted by Fumigator View Post
    That's taking a big step backwards in my opinion, it's much better to store a date or time or date/time in the database using the data types provided by the database. It's cake to format a datetime field to whatever you want, it's a pain in the arse to format an integer.
    Also, the UNIX timestamp, brought to you by the same programmers that gave us the Y2K problem a few years back, has a serious Y2.038K problem (and a Y1.970K problem) that makes it problematic for real world things like storing and doing age calculations on DOB.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #14
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You lose date comparison when you store a date as a char type, and you add unnecessary complexity when you store a date as a unix timestamp integer.
    Code:
    WHERE date1 between '2006-04-01' and '2007-02-01'
    That's impossible to do with a char field, and requires a conversion with an integer. What's the point?

    Not to mention the issues with pre-1969 dates...

    EDIT: I didn't know about that Y2.038K problem... hey that's pretty cool, I cashed in on the Y2K problem, I can now look forward to more big contracting $$$ for Y2.038K
    Last edited by Fumigator; 03-14-2007 at 04:23 PM.

  • #15
    Senior Coder
    Join Date
    Jan 2007
    Posts
    1,648
    Thanks
    1
    Thanked 58 Times in 54 Posts
    Re-read this -
    How is this

    PHP Code:
    $echo date("Y/m/d"time()); 
    More difficult than formatting in the query itself?

    You basically move the format string from PHP to SQL, I fail to see the improvement.

    Also, the UNIX timestamp, brought to you by the same programmers that gave us the Y2K problem a few years back, has a serious Y2.038K problem (and a Y1.970K problem) that makes it problematic for real world things like storing and doing age calculations on DOB.
    I'm sure servers will move to 64bit by the year 2038. Just to clarify, that is 18446744073709551616 seconds since 1970. Which means that it'll last until the year 584942417355. Should we still have a sun, I'd hope the application is no longer in use.

    and you add unnecessary complexity when you store a date as a unix timestamp integer.
    It's a different 'complexity' from datetime.

    It's easy for me to do some calculations with times, for instance when displaying a complicated calendar.

    Like I said before, both have their pros and cons. Neither is the right way. I'm always open to new methods, but I haven't seen any convincing pros that would get me to switch.

    EDIT:

    How about portability? I know I can switch my date methods/calculations over to another DB provider (Oracle, PGSQL, etc.) without problems. I don't think using Datetime would allow the same effortless transport.
    Last edited by aedrin; 03-14-2007 at 07:38 PM.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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