asked on

Formatting date for output in PHP

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

lpin

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.

gnudiff

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.

gnudiff

erwins

ASKER

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

lpin

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....

ASKER CERTIFIED SOLUTION

gnudiff

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ivanmata

just try this:

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

and that's it!!!

cheers =0)

erwins

ASKER

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