Changing How Date Displays

tycragg

I've got dates saved in a MySQL database in the format 0000-00-00, and I'd like to display them as "15 September 2006" or whatever... Any help?

devinemke

DATE_FORMAT

tycragg

devinemke wrote:
DATE_FORMAT

Thanks man. I typed in this code and I got an error (call to undefined function):

$city = mysql_result($fewComments, $i, 'city');
$date = mysql_result($fewComments, $i, 'date');
$date = (DATE_FORMAT($date, '%d %M %Y'));

What am I doing wrong?

devinemke

follow the link i posted above and start reading. DATE_FORMAT() is not a PHP function. it is a mySQL function and as such must be used within your query.

tycragg

devinemke wrote:
follow the link i posted above and start reading. DATE_FORMAT() is not a PHP function. it is a mySQL function and as such must be used within your query.

Am I closer here?

$sql = 'INSERT INTO ' . $table .
                ' (`ID`, 
	`DATE_FORMAT('$date', '%d %M %Y');`,
	`show`, 
	`city`, 
	`time`
	) 
	VALUES 
	(\'\','
	. '\'' . $date . '\',' 
	. '\'' . $show . '\',' 
	. '\'' . $city . '\',' 
	. '\'' . $time . '\'
	)';
$insert = mysql_query($sql, $DBConn)...

Litewebsite

As devinemke mentioned this is done in the sql query like this

SELECT DATE_FORMAT('2006-09-15', '%d %M %Y');

From the MySQL manual

%d = Day of the month, numeric (00..31)
%e = Day of the month, numeric (0..31)
%M = Month name (January..December)
%Y = Year, numeric, four digits

devinemke

$sql = "INSERT INTO " . $table . " (date, show, city, time) VALUES (DATE_FORMAT('$date', '%d %M %Y'), '$show', '$city', '$time')";

your code suggests that you are storing your dates as strings. don't. store your dates as dates. also why store the date and the time as two separate fields? just store one DATETIME field.

Litewebsite

tycragg wrote:
Am I closer here?

Yes! :-) You were quicker on the submit button.

I prefer storing my dates as unix timestamp.

tycragg

Litewebsite wrote:
Yes! :-) You were quicker on the submit button.

I prefer storing my dates as unix timestamp.

What's the difference? With the code I have at the moment, I get the following error:
Parse error: syntax error, unexpected T_STRING in on line 59

Any ideas?

Litewebsite

Unix timestamp shows the time (date) in seconds since "00:00:00 UTC ". I use this format because I feel it's easier to calculate with (just my personal choice). You will be fine with other formats too.

tycragg

Litewebsite wrote:
Unix timestamp shows the time (date) in seconds since "00:00:00 UTC ". I use this format because I feel it's easier to calculate with (just my personal choice). You will be fine with other formats too.

ok, that's interesting. You got any idea what I have to do to fix my code?

ahundiak

There is some value in storing dates as strings (database independence, unlimited range etc). It's easy to use php to adjust the displayed format.

$date = '2006-09-11';  // Assume this came from your database
$ts = mktime(0,0,0,substr($date,5,2),substr($date,8,2),substr($date,0,4)); // Convert to unix format
$formated = date('Y m d',$ts);

Weedpacket

One thing about storing dates/times in the database as Unix timestamps - or as bare strings - rather than the DBMS's native date/time type is that it means you can't use the DBMS's own date/time functions to perform operations (like adding a month) on the values any more - at least, not without a lot of work to convert them into the native format and then converting the results back again.

But don't look at me; I've been known to store datetimes as fractional Julian Days....

NogDog

If you don't want to change your database/query:

$date = date('j F Y', strtotime(mysql_result($fewComments, $i, 'date')));