[SOLVED] Changing the time field after importing data

[ksduded]

I am using a time field and importing data through a csv with the time as HH:MM (24 hour format with 11:00 pm as 23:00). It imports in MYSQL database as HH:MM:SS.

 

I want to convert it to 12 hour AM PM format. E.g. displaying 23:00 from the csv file to 11:00 PM.

[PFMaBiSmAd]

The format of a TIME data type is 'HH:MM:SS'

 

To output the values in a different format, use the mysql time_format() function in your query - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_time-format

[ksduded]

I tried this query

 

SELECT TIME_FORMAT('100:00:00', '%h %I');

 

but nothing happened. The data is under the field prog_time.

 

Do I need to enter that in the query as well?

 

plus i am looking to have a AM or PM function as well.. will I need to add %l in the end?

 

thanks in advance

[fenway]

I tried this query

 

SELECT TIME_FORMAT('100:00:00', '%h %I');

 

but nothing happened. The data is under the field prog_time.

 

Do I need to enter that in the query as well?

 

plus i am looking to have a AM or PM function as well.. will I need to add %l in the end?

 

thanks in advance

I don't know what that means.

[ksduded]

I tried this query

 

SELECT TIME_FORMAT('100:00:00', '%h %I');

 

but nothing happened. The data is under the field prog_time.

 

Do I need to enter that in the query as well?

 

plus i am looking to have a AM or PM function as well.. will I need to add %l in the end?

 

thanks in advance

I don't know what that means.

 

ok i will try to explain again.

 

I am using PhpMyadmin to control the MYSQL features. I have a database with a table. It has a prog_time field which has the type 'time'.

If I enter type in an input as 23:00, it is stored as 23:00:00. I want the time format to store it as 11:00 PM.

 

I entered in SQL

 

SELECT TIME_FORMAT('100:00:00','%h %I)

 

but nothing happened.

 

so my question is, how do I change the format of the prog_time field to show an input of 23:00 to 11:00  PM

 

thanks

[fenway]

If I enter type in an input as 23:00, it is stored as 23:00:00. I want the time format to store it as 11:00 PM.

 

so my question is, how do I change the format of the prog_time field to show an input of 23:00 to 11:00  PM

You DON'T change the format of the database field -- you format the field when you SELECT this column back.  Does that make sense?

[ksduded]

ok, so basically when I output it through php, i will use the different formatting types

[fenway]

ok, so basically when I output it through php, i will use the different formatting types

Sure, but what I'm saying is that TIME_FORMAT() allows you get back the desired format from the database.

[ksduded]

ok, so basically when I output it through php, i will use the different formatting types

Sure, but what I'm saying is that TIME_FORMAT() allows you get back the desired format from the database.

 

if i want to show a 11:00 PM time format by taking a data 23:00:00 stored in the prog_time field, what php code will i require?

 

I have been trying a lot of different functions, but can't work it out.

 

thanks in advance

[fenway]

You don't need any php code... use TIME_FORMAT(), as suggested above a number of times.  Post your query.

[ksduded]

ok so i am using this query

 

SELECT TIME_FORMAT('prog_time', '%h %I %P');

 

and this is how i am using it in PHP

 

while($info = mysql_fetch_array($result))

{

if($info['prog_date'] == $selecteddate)

{

$programtime = $info['prog_time'];

$ptime = mysql_query("SELECT TIME_FORMAT('$programtime', '%h %i %P'");

echo .$ptime;

}

}

[fenway]

You can't echo $ptime like that... it's a result set!  You need to (a) run mysql_fetch_assoc() and get back each row and (b) alias the expression to be able to reference it more easily.  In principle, you can cheat with mysql_result(), but it's bad practice.

 

Also, I thought this was coming from data stored in the DB.

 

[ksduded]

You can't echo $ptime like that... it's a result set! You need to (a) run mysql_fetch_assoc() and get back each row and (b) alias the expression to be able to reference it more easily. In principle, you can cheat with mysql_result(), but it's bad practice.

 

Also, I thought this was coming from data stored in the DB.

 

 

sorry for the noob questions, but I am trying to get hang of this.

 

The data is stored in a database. I have already extracted all the rows by using this query:

 

$result = mysql_query("SELECT * from tablehd");

 

 

I added a few lines to fetch the array (in bold), but I get the text Array after i run the code.

 

while($info = mysql_fetch_array($result)) 
{
if($info['prog_date'] == $selecteddate)
{ 
$programtime = $info['prog_time'];
$ptime = mysql_query("SELECT TIME_FORMAT('$programtime', '%h %i %P')");
$programtime1 = mysql_fetch_array($ptime);
echo $programtime1; 
}
}

[fenway]

OK... now I understand... no need for 2nd query!

 

Change your initial query to:

 

$result = mysql_query("SELECT *, TIME_FORMAT( prog_time, '%h %i %P') AS prog_time2 from tablehd");

 

And then you can retrieve the value with "$info['prog_time2']" -- anywhere inside that while() loop.

 

I'm also not sure what that if() is doing....

 

[ksduded]

OK... now I understand... no need for 2nd query!

 

Change your initial query to:

 

$result = mysql_query("SELECT *, TIME_FORMAT( prog_time, '%h %i %P') AS prog_time2 from tablehd");

 

And then you can retrieve the value with "$info['prog_time2']" -- anywhere inside that while() loop.

 

I'm also not sure what that if() is doing....

 

 

great that nearly worked. Everything seems to be working fine, but it shows P at the end of the time rather than the AM or PM.

 

I am using MYSQL version 5.0.27

[ksduded]

OK... now I understand... no need for 2nd query!

 

Change your initial query to:

 

$result = mysql_query("SELECT *, TIME_FORMAT( prog_time, '%h %i %P') AS prog_time2 from tablehd");

 

And then you can retrieve the value with "$info['prog_time2']" -- anywhere inside that while() loop.

 

I'm also not sure what that if() is doing....

 

 

great that nearly worked. Everything seems to be working fine, but it shows P at the end of the time rather than the AM or PM.

 

I am using MYSQL version 5.0.27

 

ok i just changed a capital 'P' to a small 'p' and it worked.

[fenway]

It should be a lowercase p:

 

TIME_FORMAT( prog_time, '%h %i %p')