Hi there,

I have a timestamp in a mysql database 20040727150214 and I am just trying to get it to format out in a readable date format in php.

I have tried:

$time2 = date("F j, Y, g:i a",$testtime2);

where testtime2 is the mysql timestamp and I just echo $time2 and what it is getting me is January 18, 2038, 7:14pm

Which isnt anywhere near correct. Anyone have any ideas? thanks!

    $time = "20040727150214";

$ts = mktime( substr($time, 8, 2), substr($time, 10, 2), substr($time, 12, 2), substr($time, 4, 2), substr($time, 6, 2), substr($time, 0, 4));

echo date("r", $ts);

    this works as desired.

      There is also the MySQL UNIX_TIMESTAMP type:

      echo date("r", mysql_result((mysql_query("select mytimefield as UNIX_TIMESTAMP from mytable where myrow='$foo'"),0));

        Bah! late night postings ...

        This should actually work:

        echo date(""F j, Y, g:i a", mysql_result(mysql_query("select UNIX_TIMESTAMP(myfield) from mytable where foo='$bar'"),0));

          hi all.. Thanks for all the advice. Dale, I can't quite do the mysql changes as that statement has to stay the way it is. The change I need is on the php side of things:\

          Drew, your method worked perfect. I am curious though what is going on there. Can you explain a little as to what the substr with the three items in each are doing? It looks like it is breaking the timestamp up into segments, is that right? Thanks for all the help all!

            yeah, my code just breaks the long timestamp up into shorter pieces, capturing what we need.
            mktime takes 7 option arguments, in this order
            int mktime ( [int hour [, int minute [, int second [, int month [, int day [, int year [, int is_dst]]]]]]])

            so the first is the hour of our time, second is minute, third is second, etc...

            substr will return a portion of a string.
            the first argument is the string to break, the second is the starting offset, and the third is how many characters to go from the start.

            a substr example can be.

            echo substr("123456789", 3, 5);
            that would print 45678.
            it starts from 0 so add on position to whatever start you give.

            so all we do is capture the different parts of the timestamp and pass them to the proper places in mktime.

              Very sweet. Drew I really do appreciate the time you took to explain that. That will help me a ton in the future while working with this. You rock! 🙂

                Originally posted by dreams4000
                hi all.. Thanks for all the advice. Dale, I can't quite do the mysql changes as that statement has to stay the way it is. The change I need is on the php side of things:[/B]

                Umm, that is PHP. Nothing wrong with hitting the db again, unless it's overtrafficked...certainly won't kill your script.

                Of course, one of the great things about PHP is that there are about as many ways to skin a cat as there are logical programmers in the world ...
                😉

                  Originally posted by dalecosp
                  Bah! late night postings ...

                  This should actually work:

                  echo date(""F j, Y, g:i a", mysql_result(mysql_query("select UNIX_TIMESTAMP(myfield) from mytable where foo='$bar'"),0));

                  [/B]

                  I doubt the 2 double quotes will help, nice code though.

                  😃

                    :bemused: ROFL! Darn c'n'p --- Bunker!! Where you been?

                      Hidding in the bush waiting for the cold war to end. I just got the word so I'm out again!!!
                      [edit]no pun intended with 'bush' but if the shoe fits...[/edit]

                        Write a Reply...