Mysql help needed. - GoFuckYourself.com

acctman · 09-02-2005, 03:34 PM

i have a db field called p_time and all the entires are in unix_timestamp 1109679576 how do i convert all field entries to a format like this 8/23/2005 0:08?

this is all i've found so far
$rs=mysql_fetch_row(mysql_query("select p_time from rate_forum_posts"));
echo date("m/d/YYYY h:i ", $rs[0]);

that doesn't work when i try an to run it on the insert SQL page

woj · 09-02-2005, 03:36 PM

use -> FROM_UNIXTIME()

acctman · 09-02-2005, 04:14 PM

do you have a working example that i can work from, to put something together to run at shell?

Mr. Mike · 09-02-2005, 04:16 PM

From_unixtime()

mlj · 09-02-2005, 04:17 PM

http://dev.mysql.com/doc/mysql/en/da...functions.html

Brujah · 09-02-2005, 04:23 PM

SELECT from_unixtime(p_time) FROM rate_forum_posts

acctman · 09-02-2005, 05:31 PM

Quote:

Originally Posted by Brujah

SELECT from_unixtime(p_time) FROM rate_forum_posts

it's not giving the right format that i need 8/23/2005 0:08

mrthumbs · 09-02-2005, 05:33 PM

why would you want to do that?

mrthumbs · 09-02-2005, 05:36 PM

date("m/d/Y H:i" should put it in the right format..

YYYY gives 2005200520052005 as output. and mind the cap H...

Make sure you converted the dbase field to Varchar since INT (which is probably used to store the numeric format) doesnt support the format (which includes spaces and characters) you want.

Brujah · 09-02-2005, 05:41 PM

Quote:

Originally Posted by acctman

it's not giving the right format that i need 8/23/2005 0:08

SELECT date_format(from_unixtime(p_time),'%m/%d/%Y %H:%i') FROM rate_forum_posts

acctman · 09-02-2005, 05:42 PM

i'm migrating a forum over and the new forum has the date field in the format of 8/23/2005 0:08

acctman · 09-02-2005, 05:48 PM

Quote:

Originally Posted by Brujah

SELECT date_format(from_unixtime(p_time),'%m/%d/%Y %H:%i') FROM rate_forum_posts

THANK YOU!... i'd hug you if i could =)

09-02-2005, 03:34 PM	#1
acctman Confirmed User Join Date: Oct 2003 Location: Atlanta Posts: 2,840	Mysql help needed. i have a db field called p_time and all the entires are in unix_timestamp 1109679576 how do i convert all field entries to a format like this 8/23/2005 0:08? this is all i've found so far $rs=mysql_fetch_row(mysql_query("select p_time from rate_forum_posts")); echo date("m/d/YYYY h:i ", $rs[0]); that doesn't work when i try an to run it on the insert SQL page

09-02-2005, 03:36 PM	#2
woj <&(©¿©)&> Industry Role: Join Date: Jul 2002 Location: Chicago Posts: 47,882	use -> FROM_UNIXTIME() __________________ Custom Software Development, email: woj#at#wojfun#.#com to discuss details or skype: wojl2000 or gchat: wojfun or telegram: wojl2000 Affiliate program tools: Hosted Galleries Manager Banner Manager Video Manager Wordpress Affiliate Plugin Pic/Movie of the Day Fansign Generator Zip Manager

09-02-2005, 04:16 PM	#4
Mr. Mike Confirmed User Join Date: Aug 2004 Posts: 913	From_unixtime() __________________ Sig for sale. ICQ: 163-545-054

09-02-2005, 04:23 PM	#6
Brujah Beer Money Baron Industry Role: Join Date: Jan 2001 Location: brujah / gmail Posts: 22,157	SELECT from_unixtime(p_time) FROM rate_forum_posts __________________ Free Premium Domain Lists and Tools at Clickmojo.com For Sale: Obscenity.com

09-02-2005, 04:14 PM	#3
acctman Confirmed User Join Date: Oct 2003 Location: Atlanta Posts: 2,840	do you have a working example that i can work from, to put something together to run at shell?

09-02-2005, 04:17 PM	#5
mlj Registered User Join Date: Jun 2005 Posts: 89	http://dev.mysql.com/doc/mysql/en/da...functions.html

09-02-2005, 05:33 PM	#8
mrthumbs salad tossing sig guy Join Date: Apr 2002 Location: mrthumbs*gmail.com Posts: 11,702	why would you want to do that?

09-02-2005, 05:36 PM	#9
mrthumbs salad tossing sig guy Join Date: Apr 2002 Location: mrthumbs*gmail.com Posts: 11,702	date("m/d/Y H:i" should put it in the right format.. YYYY gives 2005200520052005 as output. and mind the cap H... Make sure you converted the dbase field to Varchar since INT (which is probably used to store the numeric format) doesnt support the format (which includes spaces and characters) you want.

09-02-2005, 05:42 PM	#11
acctman Confirmed User Join Date: Oct 2003 Location: Atlanta Posts: 2,840	i'm migrating a forum over and the new forum has the date field in the format of 8/23/2005 0:08