Re: [PHP-DB] mysql time format
on 1/6/03 8:24 PM, David Chamberlin at [EMAIL PROTECTED] appended the
following bits to my mbox:
Is there any way to set the time format so that any time values in my
SELECT call are in a speicified format (e.g., HH:MM instead of HH:MM:SS).
Basically I've got a table of a variety of different types, some of
which are times. I want to issue a select that retrieves all of the
data for a row, and have any time values be returned in HH:MM (for example).
I can (very painfully) go through all the results, figure out which ones
are time values, and then do SELECT TIME_FORMAT() on each one of those,
but that seems like a horrendous solution to a simple problem.
As John said, there's not a way (I know of) to just tell MySQL to display
times in a different format without the TIME_FORMAT calls.
Why not add the TIME_FORMAT calls to the original query rather than
performing all the extra queries? Is your first select something like the
following?
SELECT * FROM table
Even if you don't want to specify all the columns, you could add the
additional ones like this:
SELECT *,TIME_FORMAT(time_column_1,'%whatever') AS time_column_1 FROM table
If you are using a mysql_fetch_assoc function, giving the alias the same
name as the original column makes only the alias show up in your array.
If you don't know what all the time columns are, you could do a SHOW
COLUMNS FROM table query first, then use PHP to parse the results to tell
you which fields are time types, then use that result to build a query like
that above. (You could also use one of the php mysql_field_* functions.)
HTH.
Sincerely,
Paul Burney
http://paulburney.com/
?php
while ($self != asleep) {
$sheep_count++;
}
?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql time format
Paul Burney wrote: Why not add the TIME_FORMAT calls to the original query rather than performing all the extra queries? Is your first select something like the following? That's what I'd like to do, but not sure how to do that in this context. SELECT *,TIME_FORMAT(time_column_1,'%whatever') AS time_column_1 FROM table Aha! That's what I was looking for! If you don't know what all the time columns are, you could do a SHOW COLUMNS FROM table query first, then use PHP to parse the results to tell you which fields are time types, Right, that's basically what I've already done. When my class instantiates, it parses the columns and builds an array with the names of the columns that are time types. Currently I'm using that array to cycle through the results and issue TIME_FORMAT calls on all of the results, but what I wanted was to have the TIME_FORMAT in the original call. But I didn't know how to do that without listing all of the columns and also getting the result in the same name as the original. Your code snippet explains just what I wanted. Thank you! -Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] mysql time format
This is a bit of a newbie question, but I couldn't find what I wanted in the documentation. Is there any way to set the time format so that any time values in my SELECT call are in a speicified format (e.g., HH:MM instead of HH:MM:SS). Basically I've got a table of a variety of different types, some of which are times. I want to issue a select that retrieves all of the data for a row, and have any time values be returned in HH:MM (for example). I can (very painfully) go through all the results, figure out which ones are time values, and then do SELECT TIME_FORMAT() on each one of those, but that seems like a horrendous solution to a simple problem. What type of column are you using? TIME()? If so, then there's no way to do this without using TIME_FORMAT() in each of your queries. If you're using a TIMESTAMP column, you can specify the length to XX so that it only stores data in a MMDDHHMM format. ---John W. Holmes... PHP Architect - A monthly magazine for PHP Professionals. Get your copy today. http://www.phparch.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
