mySQL Timestamp to PHP problems 2

[JSProgramIA]

The Problem:
I am retrieving a timestamp field from a mySQL database running on Apache. I am trying to take this timestamp, and within PHP convert it to something actally readable.

The record is being returned as follows:

mysql>  select last_updated from hours where project_id =2;

+----------------+
| last_updated   |
+----------------+
| 20051214212002 |
+----------------+

Contribting Factors?
These records were initially generated on a windows box. Not sure if that makes a difference.

My Ideal Results:
I would like to take the above result and be returned the following, which I could use in PHP:

December 14, 2005 8:00pm

Anyone know of a way?

Regards.

 

[vacunita]

Have you looked at the [blue]date()[/blue] funtion in php.

http://www.php.net/manual/en/function.date.php

it takes a timestamp and formnats it however you want it to display.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[JSProgramIA]

Tried that. I am being returned the following incorrect date based on the example above:

12-31-1969 15:59:59

 

[vacunita]

What kind of timestamp is it? a MYSLQ timestamp? if that is the case you can use the code snippet found here to convert it.

http://www.php.net/manual/en/function.time.php

Convert Mysql timestamp into Unix Timestamp.

<?
function format_data($mysql_timestamp){
preg_match('/(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})/',
$mysql_timestamp,$pieces);
$unix_timestamp = mktime($pieces[4], $pieces[5], $pieces[6],
$pieces[2], $pieces[3], $pieces[1]);
return($unix_timestamp);
}
?>

Then you can use the date() function to format it.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[JSProgramIA]

PERFECT! What a great function.

This worked as I wanted:

<?php echo date("F j, Y, g:i a", format_data($row_Recordset1['when_it_happened'])) ?>

Thanks! This one will be going in my library for sure.

 

[vacunita]

Glad it was helpfull.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[KarveR]

always makes me flinch when I see so much code instead of a more thoughtful db query:


mysql> select date_format('20051214212002', '%M %d %Y, %H:%i');
+--------------------------------------------------+
| date_format('20051214212002', '%M %d %Y, %H:%i') |
+--------------------------------------------------+
| December 14 2005, 21:20 |
+--------------------------------------------------+


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[JSProgramIA]

thanks for the "thoughtful db query"

some of us, however, may not know there was even such a query available, until someone like yourself shares their knowledge.

as you just did.

thanks.

 

[KarveR]

Aye, very true MySQL is a powerful tool with a manual to rival the Hitch-Hikers Guide to the Galaxy.

Getting the balance right between readable queries and readable PHP can be troublesome at times and finding out which will perform best or prvide the best results can be somewhat trying.

The mysql forum is a very good resource where a number of us post in both.

http://tek-tips.com/threadminder.cfm?pid=436

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[jpadie]

has anyone done a benchmark between the php and the mysql variant of this kind of thing? like so many of these issues I often wonder whether the "pure" way of achieving a goal actually benefits in terms of time, in the long run.

a bit like sql optimisation: paying a tech 1k per day for optimisation versus shoving more memory/processors in a box.... price of hardware these days beats the price of employment hands down!