Formatting date for output in PHP

erwins
erwins used Ask the Experts™
on
In MySQL database, I have a date field called Bid_Date with a type of timestamp(14).  In PHP, how can I convert this so it prints a more readable string?

ex: 20021114224920 to Nov 14, 2002 22:49:20

So far, I've tried to use printf and sprintf with no luck.

Thanks,
Erwin
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
you must convert the timestamp using date. try this one :
 $newBidDate = date ("M d, Y G:i:s",$row["Bid_Date"]);

$row["Bid_Date"] is a variable that store your data from database.
 then you just printf the newBidDate variable.

Commented:
I would recommend simply converting timestamp in the SELECT query in db itself, using a MySQL function, so that PHP already receives formatted date, if that is usable for you.

Generally, I would say it is better to leave as much processing to SQL server as possible, especially formatting etc.
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Author

Commented:
I tried lpin's suggestion, and although the formatting comes out correct, the date displayed is wrong. It is displaying Jan 18, 2038 when it should be Nov 15, 2002.

In my MySQL database, I'm using the timestamp(14) for the date field, which automatically stores the date when a record is updated or included (eg. 20021115025545).  I just realized, that this date should be an integer which is an offset from some base date, not a real date, hence the wrong displayed date.  Please suggest a date format to use for my date field in MySQL and the best way to populate it for the date a transaction is processed?  My php book doesn't cover this much.  Maybe I need a new book.

Thanks,
Erwin

Commented:
i thought my first suggestion was correct.
if your timestamp is correct and it's store 15 nov 2002 in timestamp format, when you use the function that i gave it will return the correct date....
And there's one thing that you can try... try echo to print the date, not printf....

Commented:
I looked over mysql docs, which are a bit vague on storage of timestamp data (ie. whether it is stored internally as Unix timestamp or some other form), but one of the following should do what you need:


FROM_UNIXTIME(your_timestamp_field,'%b %e, %Y')

or simply DATE_FORMAT(your_timestamp_field,'%b %e, %Y')

If you need a bit different formatting, simply have a look at the page the link to which I posted a bit above. There's a table with the formats.

Commented:
just try this:


strftime("%b %d, %Y %H:%M:%S",$row["Bid_Date"]);

and that's it!!!

cheers =0)

Author

Commented:
It seems like the only one that works is the MySQL function DATE_FORMAT.  I tried the PHP strftime("%b %d, %Y %H:%M:%S",$row["Bid_Date"]);
, and similar to date("M d, Y G:i:s",$row["Bid_Date"]); this is returning correct format but wrong date.  I think the PHP functions are expecting the date to be an integer which is an offset from a base date, and not an actual date string or number.  I'm running out of time on this project or else I would experiment more.

Thank you all for your help.

Erwin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial