Understanding Dates in mySQL

displaying date plus deleting expired records

[old_expat]

In a new very small mySQL database I'm making, I am having difficulty understanding several issues revolving around a date column .. which is an expiration date.

It appears that the default format for date is 0000-00-00. Is that reversible for showing in the information displayed? If so, is it also possible to use dd-mm-yy rather than dd-mm-yyyy?

How do I change the date fields in the database structure?

Also, some folks here were nice enough to give me some code that would delete expired records. So I would like to keep that feature available. Do I need to select any particular item from the dropdown? ie, unix datestamp?

[txbakers]

mySQL has a DATE_FORMAT command available which allows you to display the date any way you like, including with custom separators. To do a standard US date (mm/dd/yyyy) you would write:

SELECT DATE_FORMAT(datefield, '%m/%d/%Y') as formatteddate

All the format codes are in the mySQL documentation, either on the web or in your docs folder.

HOWEVER: If you require sorting by date, you shouldn't use the formatted date. Always use your original date for the ORDER BY

[old_expat]

I can't make this work. I'm probably doing something wrong ..

@MYSQL_SELECT_DB($database) or die("Error");
$query="select DATE_FORMAT(expires, '%m %d %Y') as formatteddate";

I don't get an error message, but the date remains yyyy-mm-dd

[txbakers]

@MYSQL_SELECT_DB($database) or die("Error");
$query="select DATE_FORMAT(expires, '%m %d %Y') as formatteddate";

I don't get an error message, but the date remains yyyy-mm-dd

If today is April 10, 2006, your formatting will leave the spaces: 04 10 2006

if you want the slashes, you have to put those in.

For the error, be sure you are referencing the field name of "formatteddate" rather than expires in your code.

[old_expat]

""@MYSQL_SELECT_DB($database) or die("Error");
$query="select DATE_FORMAT(expires, '%m %d %Y') as formatteddate";

I don't get an error message, but the date remains yyyy-mm-dd""

"If today is April 10, 2006, your formatting will leave the spaces: 04 10 2006

if you want the slashes, you have to put those in.

For the error,..."

I'm NOT getting an error .. just not getting the changed date format

"... be sure you are referencing the field name of "formatteddate" rather than expires in your code. "

The field name in my database is "expires".

[txbakers]

The field name in my database is "expires".

That may be true, but you don't want to display that in your HTML, right?

You want to display the newly created fieldname "formatteddate" instead. when you do a command, you give the newly formatted field an alias. That is what you reference in your code.

[old_expat]

Okay, this time I used

@MYSQL_SELECT_DB($database) or die("<div class=contentdiv>Error</div>");
$query="SELECT DATE_FORMAT(datefield, '%m/%d/%Y') as formatteddate";

and still get 0000-00-00 (not even the "/")

[old_expat]

What's odd about this .. and yet typical ..

The more I search for this on the web, the more "answers" I find .. and all are slightly different ..

.. and all show the code in isolation (not in context)

.. and I can't get any of the examples to work *for me*.

I believe one of the failings of forums like these is that the person posting the code makes an assumption .. that the person reading the solution understands the context.

And in my case, that is virtually always a bad assumption.

[edited by: old_expat at 1:19 pm (utc) on April 11, 2006]

[txbakers]

post the code where you actually display the formatteddate on the screen.

that might clear up confusion.

I've been using this for years without incident.

[old_expat]

$query .= " ORDER BY expires DESC";
$result=mysql_query($query);
$numrow=mysql_num_rows($result);
if ($numrow) {
while ($coupons = mysql_fetch_array($result))
{
echo('
<tr>
<td>'.$coupons['points'].'</td>
<td>'.$coupons['item'].'</td>
<td>&pound; '.$coupons['purchase_gbp'].'</td>
<td>'.$coupons['expires'].'</td>
<th>'.$coupons['code'].'</th>
</tr>
');
}
}
?>

[txbakers]

<td>'.$coupons['expires'].'</td>

There is your problem.

You are STILL referencing the original data field.

You need to reference the ALIAS of the formatted one.

.$coupons['formatteddate'].

[old_expat]

Here is what I tried.

$query="SELECT DATE_FORMAT(datefield, '%m/%d/%Y') as formatteddate";

then

.$coupons['formatteddate'].

The display was empty.

so I tried

$query="SELECT DATE_FORMAT(expires, '%m/%d/%Y') as formatteddate";

also empty

I'm not understanding "ALIAS" .. maybe you could point me to a tutorial?

[txbakers]

[dev.mysql.com...]

[old_expat]

I think I'll just go to one of the coding sites and get this done for me; as I can't make heads ot tails of the examples given above .. or on that page.

The page is about selecting by date rather than selecting format (as far as my limited abilities can discern), and that's not necessarily what I want to do. And all the extra code in the line is confusing.

I can get queries working, but not this date format.

Just a suggestion, if someone asks a really newbie question, something like you have posted above doesn't help all that much if you only show part of a line. IOW, if the line needs to be

$query .= " ORDER BY expires DESC";

but all you show is

ORDER BY expires DESC

It can be extremely confusing .. to someone like me.

Anyhow, thanks for your trouble.

just my 2 satang

[coopster]

A column ALIAS is a temporary name that you are giving to a column in the result set you want returned to you. Take the following query for example:

$query="SELECT myname, DATE_FORMAT(expires, '%m/%d/%Y') FROM table";

If you are using PHP to retrieve that in your result set, how do specify it's name? Well, the first column

myname

would be easy, but what about that second column in your result set?

while ($coupons = mysql_fetch_array($result)) { 
 echo(' 
  <tr> 
   <td>'.$coupons['myname'].'</td> 
   <td>'.$coupons["DATE_FORMAT(expires, '%m/%d/%Y')"].'</td> 
  </tr> 
 ');  
}

Yuck. OK, I am going a bit overboard to make a point, but hopefully you see what a column ALIAS can do for you now and why we use them. Let's look at the query again and how we might use the ALIAS now:

$query="SELECT myname, DATE_FORMAT(expires, '%m/%d/%Y') AS formatteddate FROM table"; 
... 
while ($coupons = mysql_fetch_array($result)) { 
 echo(' 
  <tr> 
   <td>'.$coupons['myname'].'</td> 
   <td>'.$coupons['formatteddate'].'</td> 
  </tr> 
 ');  
}

Much nicer.

Now, functions in MySQL that expect date values such as DATE_FORMAT usually accept datetime values and ignore the time part. Functions that expect time values usually accept datetime values and ignore the date part. The point being, first make sure the column you are using is of the correct type. Second, make sure it contains the expected data format that MySQL is ready to work with. I think you have both instances covered here but to be certain you can DESCRIBE your table to see how your

expires

column is defined. Next, you can query the table to see if there is data in the column or if it is just zeros. If you are querying and formatting a zero-filled column then the DATE_FORMAT is going to simply move the zeros around for you and show you the same information formatted a bit differently. Example:

SELECT 
 '0000-00-00' AS expires, 
 DATE_FORMAT('0000-00-00', '%m/%d/%Y') AS expiresformatted 
; 
-- 
-- Returns: 
-- 
+------------+------------------+ 
¦ expires    ¦ expiresformatted ¦ 
+------------+------------------+ 
¦ 0000-00-00 ¦ 00/00/0000       ¦ 
+------------+------------------+