mysql timestamp field...

fleshbox

I have a MySQL table containing a timestamp field. I need to display this timestamp in a more readable way ie.

07-18-2002 03:37 AM

What is the best way to go about converting 200207180337 to the previously mentioned format?

can I do it in PHP after the query or is there a way to structure the query to return the format I need?

chadedge

Use the date(); function.

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

Example:

<?
$goodtime="200207180337"; //your timestamp
$goodDate=date("m-d-Y h:i A",$goodtime);
?>

One important issue: You may need to convert the timestamp from MySQL to Unix, if you're indeed storing the date as a MySQL timestamp.

You can read about that in the manual w/ the link above.

UPDATE: I noticed you're not really using a timestamp.
2002(year)07(month)18(day)07(hour)37(minute).

Where'd you get that in the first place? Or am I just nuts and that's what MySQL uses for timestamps?

fleshbox

thats the standard format for mysql timestamps

and thanks... I wasn't sure if the date() function would work or not...

I've been using php for about a month and a half now... maybe a tad longer...😃

chadedge

date uses the unix timestamp format, so your timestamp wont work.

You're best bet is to split the data out, trim it, and use the items in an array to change your format.

a bit spaghetti, but who doesn't like spaghetti?

sijis

You might alread have a solution, but this worked for me.
I'm am also using a timestamp type in my db.

i wanted the date to look like this:

10/9/02

$date = date("m/j/y",time($date_string));

for some reason, when i'd do

$date = date("m-d-Y h:i A",$date_string);

it would display some bad data and i'm not sure why.

I'm just showing you another, possible, option.

fleshbox

ok, like you stated date does not work (

only one problem... how exactly would I go about breaking up the mysql timestamp into an array... I mean I know how to build an array, but I know nothing about breaking a string apart.....

fleshbox

thanks sijis

That worked perfectly!

very very mucho gracias

chadedge

To convert a MySQL timestamp to a Unix-style timestamp, use MySQL's UNIX_TIMESTAMP function.

For Example:

$result=mysql_query ("SELECT UNIX_TIMESTAMP(timestamp_column) as epoch_time FROM table"); 

$unix_timestamp = mysql_result ($result, 0, 0);

Taken from the PHP Manual Notes.