[Resolved] mysql timestamp(6) PHP

schumanna

Hello everyone,
I have made a mysql table containing date_added timestamp(6) and have the PHP calling on it to display it with

date("d.m.Y", $row[8]).

$row[8] being the query to it. But all I am getting is 01.01.1970. I have searched and search and am not finding much how to solve this.

Anyone know to to get it to display the currect date when the data way entered into the the table?

Thanks

schumanna

nemonoman

If you echo the content of $row[8], I would expect you will find something like:
200406

timestamp (6) will only show
4 digit year
02 digit month

PHP date() works from a unix timestamp

date("d.m.Y", 200406)

would show the date as 200406 seconds after 1 Jan 1970 -- a little over 2 days worth of seconds

You SHOULD be getting 3.1.1970. That you are getting 1.1.1970 suggests that $row[8] is null.

My suggestion would be to change the column definition to timestamp 8: example 20040617

Then format the date using MySQL

SELECT DATE_FORMAT(myTimestamp, '%d.%m.%Y')
FROM myTable

which will return the value as a string in the format you desire.

schumanna

I'm not sure really how to do this.

I have changed date_added timestamp(8) and am now getting 20.08.70 with the php date(). I understand what you are saying but not sure how to build this in. Here is my script.

// Print the first, last name and Add-on name , Added-date

echo "\n<tr>\n\t<td bgcolor=\"maroon\">" .
	 "<b><font color=\"white\">" .
	 $row[1] . 
	 " " .
	 $row[2] . 
	 "<b> Add-on: </b>" .
	 $row[3] .
	 "<b> Last updated: </b>" .
	 date("d.m.y", $row[8]) .
	 "</font></b></td>\n</tr>";

How do I build it in. with a full mysql_query? Sorry. 🙂

nemonoman

Let me guess, you're doing

SELECT *
FROM mytable

OK:
if the name of your timestamp column is MyTimestamp,

SELECT *, DATE_FORMAT(MyTimestamp, '%d.%m.%Y') as FormattedDate
FROM mytable

if you are using someting like $row=mysql_fetch_array($result)
then
$row['FormattedDate'];

will contain your timestamp already formatted the way you want.

echo "\n<tr>\n\t<td bgcolor=\"maroon\">" .
"" .
$row[1] .

" " .
$row[2] .

" Add-on: " .
$row[3] .
" Last updated: " .
$row['FormattedDate'] .
"</td>\n</tr>";

schumanna

Well I have added now

$query = "SELECT *, DATE_FORMAT(8, '%d.%m.%Y')as FormattedDate FROM mytable ";

and

// Print the first, last name and Add-on name, date_added
	echo "\n<tr>\n\t<td bgcolor=\"maroon\">" .
		 "<b><font color=\"white\">" .
		 $row[1] . 
		 " " .
		 $row[2] . 
		 "<b> Add-on: </b>" .
		 $row[3] .
		 "<b> Last updated: </b>" .
		 $row['FormattedDate'] .
		 "</font></b></td>\n</tr>";

is this right? I am not getting any errors but not getting any date displayed. 🙁 Stupid me🙂

nemonoman

To help us diagnose this: add this line to your code:

print_r($row);

then post the output of the array:

schumanna

Thanks for your help!!! 🙂

The output I gat was:

Array ( [0] => 1 [1] => firstname [2] => Schumanna [3] => MY Addon name [4] => To much work. wuahahah [5] => aerz [6] => email_address@aschumann.de [7] => http://www.aschumann.de [8] => 20040616 [9] => )

What do you think? Thanks nemonoman!!! 🙂

[8] => 20040616 being the timestamp right? What am I doing rung. 🙂

TheDefender

Change your query to:

$query = "SELECT *, DATE_FORMAT([b]date_added[/b], '%d.%m.%Y') as FormattedDate FROM mytable ";

(Assuming by your previous posts that date_added is the name of the column containing the date information. If this isn't the case, then change that part in bold above to whatever the actual name of the column is.)

schumanna

Thanks also TheDefender!!! 🙂

Okay I changed it like you sed and still nothing so I added (just to test it). Note the above colume 9 changed

	

// Print the first, last name and Add-on name, date_added 
    echo "\n<tr>\n\t<td bgcolor=\"maroon\">" . 
         "<b><font color=\"white\">" . 
         $row[1] .  

         " " . 
         $row[2] .  

         "<b> Add-on: </b>" . 
         $row[3] . 
         "<b> Last updated: </b>" . 
         $row[9] .
         "</font></b></td>\n</tr>";

and it WORKED. Is this right? Withyout the $query we are using now it will not work so it must be all right now, or? Thanks mill all! Your seperb!

I don't fully understand why it works with row[9] but it does. 🙂

TheDefender

Because in the query, '%d.%m.%Y' tells it to pull the format as dd.mm.yyyy.

If you want to parse the yyyymmdd one (column 8), then you gotta take out the periods and shift the formatting around a bit, so your query would go something more like this:

$query = "SELECT *, DATE_FORMAT(date_added, '%Y%m%d') as FormattedDate FROM mytable ";

Sorry I missed that before.

schumanna

It works! SUPERB!!! Thank you!!

🙂

schumanna

How do I mark this thread as resolved? 🙂

TheDefender

There is a link that reads "Mark Thread Resolved" at the bottom.

P.S. Have a nice day!