Convert MySQL timestamp to PHP date/time.

leatherback

Hi All,

Using a timestamp(14) colum, I log the moment of inserting certain data in a MySQL database.

I would like to be able to call this data, and display / edit this as normal data/time. When I query the database, all I get is a string with the date like this: YYYYmmddHHmmss.

How can I best get this into a date format, which PhP can process? Or: If i am overlooking something obvious: How can I further process it (Call the day, month, year, hours, minutes, seconds independently for instance)

Thanks!

hand

http://www.mysql.com/doc/en/Date_and_time_functions.html

mysql> select * from ts;
+----------------+
| timestamp      |
+----------------+
| 20021117123419 |
| 20021117123421 |
+----------------+
2 rows in set (0.00 sec)

mysql> select week(timestamp) from ts;
+-----------------+
| week(timestamp) |
+-----------------+
|              47 |
|              47 |
+-----------------+
2 rows in set (0.00 sec)

mysql> select year(timestamp) from ts;
+-----------------+
| year(timestamp) |
+-----------------+
|            2002 |
|            2002 |
+-----------------+
2 rows in set (0.00 sec)

mysql> select date_format(timestamp,"%W %M %Y") from ts;
+-----------------------------------+
| date_format(timestamp,"%W %M %Y") |
+-----------------------------------+
| Sunday November 2002              |
| Sunday November 2002              |
+-----------------------------------+
2 rows in set (0.00 sec)

mysql>

leatherback

Hi Hand,

That is a great help!
Just one more step now..

How would I refer to this date in PhP? Where do I get the values from?

this is what I do:

$Query = "select date_format(Timestamp,\"%Y %m %d %H %m %s\") from session";

$Results =@ mysql_query($Query);

while ($row =@ mysql_fetch_array($Result))
{
echo $row[Timestamp];
}

But get no results from the row[Timestamp]. Running it directly on my database shows that the query does what it should. But how do I extract the data from the result? What do I refer to?

Thanks!

hand

$Query = "select date_format(Timestamp,'%Y %m %d %H %m %s') AS wonderbra from session"; 
$Results = @mysql_query($Query); 
while ($row =@ mysql_fetch_array($Result))  { 
      echo $row[wonderbra]; 
}

// .. or

$Query = "select date_format(Timestamp,'%Y %m %d %H %m %s') AS Timestamp from session"; 
$Results = @mysql_query($Query); 
while ($row =@ mysql_fetch_array($Result))  { 
      echo $row[Timestamp]; 
}

leatherback

Haha!

I think I prefer the wonderbra! 😉

I was actually just about to send a message that I found that "as" option too. But thanks! <

Cheers!