[SOLVED] datetime manipulation

[smerny]

mysql datetime() on my hosting server is -7 GMT... in the format of "2009-08-12 08:44:34"

 

I know how to format within the date() function... like date("F j, Y, g:i a") for example, but I don't know how to format an already saved date that I am taking from the mysql database...

[PFMaBiSmAd]

The mysql date/time functions - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

 

To format a DATETIME any way you want in a query - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

 

[smerny]

thanks

[smerny]

actually, i attempted to use your date_format() function, but keep getting this error

 

Warning: date_format() expects parameter 1 to be DateTime, string given

 

the sql field is datetime and is in the format of "2009-08-12 08:44:34"

[PFMaBiSmAd]

The mysql DATE_FORMAT() function that I posted a link for and mentioned using in a query is not the same as the php date_format() function.

 

You would need to post your code to get specific help with what you are doing wrong in it.

[smerny]

well i use dates in several places... but one example would be...

 

$search = "SELECT * FROM messages WHERE ID_convo = '".$convo."'";

$result = mysql_query($search) or die ("SQL Error: ". mysql_error());

$num_msgs = mysql_num_rows($result);

while ($posts = mysql_fetch_array($result))

{

 

then within that loop i have... echo $posts['time']

 

and it comes out in the format of "2009-08-12 08:44:34", i want to know how to change that format and also if the user is logged in and has a timezone set, adjust for that

[smerny]

i was thinking there would be a simple php function for it... i guess i could rip apart the string myself but if that isn't necessary....... anyone?

[p2grace]

Changing your query to use DATE_FORMAT is the best option.  You could try using strtotime() and seeing if that functional can accurately interpret the DATETIME sql format.

 

$time = strtotime($posts['time']);

[smerny]

yea i tried the strtotime, didnt help... how would i change format with a DATE_FORMAT sql?

 

format to something like... 8/12/2009, 8:44 AM

[p2grace]

URL: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

 

Example:

 

$query = "SELECT id,DATE_FORMAT(time,'%m/%d/%Y, %l:%i %p') AS time FROM... ";

[smerny]

thanks