[PHP/MySQL] formatting the date

[FaderMonkey]

I'm still just learning PHP and MySQL and I'm having a problem understanding how to print out the date of a table entry. I can print it out so that it looks like this:

20040712112214

....but how do I format it so that it makes sense? I know there is a DATE_FORMAT function in MySQL, but I guess I don't know how to use it cause I can't get it to work.

[SinisterMotives]

Always store your timestamps as a Unix date integer and use the PHP date() function to format it as human-readable text.

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

[FaderMonkey]

The type of field I have my date/time setup as is TIMESTAMP. When I'm defining and running my query, I'm doing it like this:

$query = 'SELECT * FROM weblog2 ORDER BY entrydate DESC'
if ($r = mysql_query ($query))
while ($row = mysql_fetch_array ($r)) {
print ...

How do I go about using the PHP date() function? Where do I go from here?

[Rawb]

SinisterMotives: I thought I was the only one that knew that secret... now it's out of the bag, everybody is going to be able to do time easily.... now if they just could not learn about setting the locale and printing the date normally to adjust for timezone settings, I could have cornered the market.

FaderMonkey: A great way to handle TIMESTAMP columns is either to alter them into INT columns that are unix timestamps, or if that isn't an option, on each and every select and insert, you can use UNIX_TIMESTAMP(column_name) (for selects) and FROM_UNIXTIME(timestamp) (for inserts/updates). MySQL's date/time functions are really lacking, so I normally just avoid them and use PHP all of the way. It's a lot easier, but still harder than Postgres's full timezone support for it's date/time columns.

[SinisterMotives]

Originally posted by FaderMonkey
How do I go about using the PHP date() function? Where do I go from here?

The date() function requires a Unix time integer as its second argument, so it won't work with the MySQL timestamp. You'll need to use the MySQL UNIX_TIMESTAMP function in a query to get the timestamp into Unix format, as aoeuhtns suggests.

To answer your question - how to use the date() function: the first argument to date() is a string built up of the flags shown in the table on the manual page I linked you to. Example:

PHP Code:


<?php
  $unix_time = mktime();
  echo "The Unix representation of the current date and time is " . $unix_time ."<br>";
  $human_time = date("F j, Y, g:i a", $unix_time);
  echo $human_time;

?>

would print:

The Unix representation of the current date and time is 1089736421
July 13, 2004, 10:33 am

[FaderMonkey]

Originally posted by SinisterMotives
You'll need to use the MySQL UNIX_TIMESTAMP function in a query to get the timestamp into Unix format, as aoeuhtns suggests.

Please excuse my ignorance, but how do I go about doing that? So far I'm defining my query like this:

PHP Code:

$query = 'SELECT * FROM weblog2 ORDER BY entrydate DESC' 


How do I add the UNIX_TIMESTAMP function?

[SinisterMotives]

You'll probably have to replace the asterisk with the actual names of the fields:

PHP Code:

$query = 'SELECT field1, field2, field3, UNIX_TIMESTAMP(entrydate) FROM weblog2 ORDER BY entrydate DESC'; 


I've never actually worked with MySQL timestamps, so I don't know if that will work correctly in your case. That's just how I think it should work.

[FaderMonkey]

That worked. Thank you, thank you, thank you, thank you, thank you for your help.

[FaderMonkey]

Oops...I guess I was wrong. The date() function is printing the current time instead of the timestamp from the data base. Here's how I wrote it:

PHP Code:

$datetime = date("F j, Y, g:i a, $entrydate"); 


Is there something else I'm missing?

[SinisterMotives]

The second double-quote goes after the a in the first argument. $entrydate is the second argument, not part of the first argument.

[SinisterMotives]

You're going to have to get the MySQL timestamp into a Unix date integer format before you can use date() on it too. There's no telling what the result will be if you just supply $entrydate as an argument to date().

[FaderMonkey]

When I do this:

PHP Code:

 $query = 'SELECT UNIX_TIMESTAMP(entrydate), entrytitle, entrytext FROM weblog2 ORDER BY entrydate DESC LIMIT 10';

while ($row = mysql_fetch_array ($r)) {

 $datetime = date("F j, Y, g:i a", {$row['entrydate']);

print "<p><br><b>{$row['entrytitle']}</b></p>
<p>{$row['entrytext']}</p>
<p>$datetime</p>";} 


I get a parse error for the line with the date() function in it.

When I do this:

PHP Code:

 $query = 'SELECT UNIX_TIMESTAMP(entrydate), entrytitle, entrytext FROM weblog2 ORDER BY entrydate DESC LIMIT 10';

while ($row = mysql_fetch_array ($r)) {

$datetime = date("F j, Y, g:i a", $entrydate);

print "<p><br><b>{$row['entrytitle']}</b></p>
<p>{$row['entrytext']}</p>
<p>$datetime</p>";} 


...it prints the date as December 31, 1969, 4:00 pm.

[MrFlux]

In that first one there you have a { before $row['entrydate'] which is causing the parse error.

[FaderMonkey]

Originally posted by MrFlux
In that first one there you have a { before $row['entrydate'] which is causing the parse error.

Yep, that fixed the parse error, but it's still printing the date as December 31, 1969, 4:00 pm.

[SinisterMotives]

The date() function usually returns a representation of 12/31/1969 if the supplied Unix integer is not a valid date on or after midnight 01/01/1970. The problem is further up in your code. Not sure where.

[SinisterMotives]

Oh. Try this:

PHP Code:

$datetime = date("F j, Y, g:i a", $row['entrydate']); 


[FaderMonkey]

I ended up doing it this way and it seems to be working:

PHP Code:

$query ="SELECT entrytitle, entrytext, entrydb,";
$query.=" DATE_FORMAT(entrydate, '%M %d, %Y %T') AS date";
$query.=" FROM weblog2 ORDER BY entrydate DESC LIMIT 10";
$result=mysql_query($query);
while (list($entrytitle,$entrytext,$entrydb,$entrydate) = 
mysql_fetch_row($result)) 


I have another question about this though. I got this code from someone else....a website about making a simple weblog....so I want to understand exactly how it's working. The seperate lines for defining the query ($query= and then $query.=)....is that just a way of setting different queries to the same variable? Does that question make sense?

[SinisterMotives]

The dot operator simply concatenates the three strings. It could have been written:

PHP Code:

$query ="SELECT entrytitle, entrytext, entrydb, DATE_FORMAT(entrydate, '%M %d, %Y %T') AS date FROM weblog2 ORDER BY entrydate DESC LIMIT 10";