Re: [PHP-GENERAL] Format the date as used in MySQL?

On 19 May, Todd Eddy wrote:
> I am trying to format the datestamp that is created in MySQL and it
> doesn't seem to work.  For example, in MySQL, the datestamp is shown as
> 20000515101054 (yyyymmddhhmmss)  but when I use date( "F d, Y", $date )
> where $date is what I have that entry saved as, I get January 13, 2038.
> Is there a different command to handle these kind of dates or do I have
> to format it some way?  BTW, I also wanted to include the time, but just
> wanted to check it quickly, so I don't know how the time will show up.
> 

You have that formatted as timestamp(14) by the look of things, which
returns the date already formatted, as yyyymmddhhmmss. (10.10:54 am on
15 May 2000 in your example). But what date() is expecting is a
timestamp in Unix epoch range. It's getting a value way larger than
expected and thus feeding you the date of the 'year2038' bug :-)

I think there is a function in mySQL to return a formattted date, but a
quick look failed to find it (DATEFORMAT??). You may continue the
search, if you like.

There are several other ways of skinning this particular cat. Firstly,
you could parse the actual value returned and construct your date and
time output therefrom - this would be good practice in string handling
but not the best way of doing it.

Secondly, you could feed the parsed output to mktime, which would return
you a suitable value of timestamp to feed to date. Slightly better, but
still all that parsing....

Thirdly, there is a mysql SELECT function UNIX_TIMESTAMP which can be
called with a timestamp field as argument, which returns a suitable
timestamp to feed to date(). Hmm, looks good - mysql does all the work
here :-)

And there are probably xteen other ways of doing it.

<Takes off teacher's hat>

Cheers
-- 
David Robley                        | WEBMASTER & Mail List Admin
RESEARCH CENTRE FOR INJURY STUDIES  | http://www.nisu.flinders.edu.au/
AusEinet                            | http://auseinet.flinders.edu.au/
            Flinders University, ADELAIDE, SOUTH AUSTRALIA