Timestamp to m/d/y date

mrgrammar

I have a timestamp field in a mysql db. On the report, I need to display the date of the record as mm/dd/yyyy. How do I pull out the date from the timestamp and format it as mm/dd/yyyy?

kperoni

<?
$yourstamp=strftime("%m/%d/%Y ",strtotime("$yourstamp"));

That should do the trick..

pyro

Something like this might be eaiser/better:

<?PHP
$date = date("m/d/Y", $timestamp);
?>

mrgrammar

Originally posted by pyro
Something like this might be eaiser/better:
<?PHP
$date = date("m/d/Y", $timestamp);
?>
[/B]

I used the function above and the month and day displayed fine. The date in the timestamp is correct reading 2003. However, the year using the code above shows 2038. Why and how do I fix it?

Mr. Grammar

pyro

You must be feeding it an incorrect timestamp, as this works fine:

<?PHP
$timestamp = time();
$date = date("m/d/Y", $timestamp);
echo $date;
?>

mrgrammar

That's what I thought at first. However, it seems to be pulling the correct timestamp. The code that I'm using to display the timestamp data is:

while ($qry = mysql_fetch_array($getorders))
{
$getdate = $qry[orderdate];
$orderdate = date("m-d-Y", $getdate);

echo "<b>Date:</b> ".$orderdate;
}

echo $getdate; // displays 20031111134416, the timestamp value

echo $orderdate; // displays 01-18-2038, the incorrect date (which indicates that my earlier post was incorrect when I said that the m/d are working properly)

Therefore, it seems to be pulling a valid timestamp. Are there other factors that I need to check? I must be doing something wrong here.

mrgrammar

Pyro, nice Flash splash on your site, btw.

pyro

That time is indeed wrong. I just grabbed a current timestamp, and it returned: 1069185610

How did you get the other timestamp?

Cheers for the good words about my intro... 🙂

mrgrammar

The timestamp that I displayed is the default timestamp that our MySQL db puts in when a date and time is not specified.

I wondered about this back when I first saw it until I realized that when the date and time are not specified, it is putting in the actual date and time.

20031111134416

2003 year
11 month
11 date
134416 1:44 p.m. (and 16 seconds)

superwormy

Indeed, timestamps in MySQL are stupid, and not stored as seconds from UNIX epoch, but YYYYMMDDHHSSMM format instead.

You might consider looking at the MySQL date / time functions, as they provide a means to format it, or you might consider storing the seconds since the UNIX epoch in an INT field, or storing it in a datetime field as 2003-11-11 23:32:14 instead.

portaloo

$sql = mysql_query("select userID, DATE_FORMAT(startdate, '%a %d %M %Y %h:%i:%s') as stdate from thread where forumID='$forumID' order by threadID desc");

i know this works with datetime which is what i always use

this will give:
Mon 17 November 2003 01:31:47