Updating Record with Results from Funtion

[430 Man]

For goofy reasons I won't explain, (legacy code) I need to have a separate column in my database with a human readable time. (can't strtotime)

 

In other words, I have 'time' already and it has values like 1815 or 1100 etc... I added an 'hrtime' to the db and now I need to take the 'time' run it thru a function and put the value in hrtime.

 

Example I have:

event      time

-----------------

Picnic    1300

Party      1930

 

and I need

 

event    time    hrtime

--------------------------

Picnic    1300    1:00 pm

Party    1930    7:30 pm

 

 

Here is the function to convert the time from military to standard in case you care:

// function to convert mil time to standard time
function miltostandard($mtime)
{
$milTime = $mtime;
$amPm = 'am';

if (intval($milTime > 2359) || strlen($milTime) < 4)
{
echo $time . "<br />";
die("Please use a valid time");
}

$milTimeHours = substr($milTime, 0, 2);
$milTimeMinutes = substr($milTime, 2, 2);

if (intval($milTimeHours >=12))
{
$amPm = 'pm';
if (intval($milTimeHours > 12))
{
$standardTimeHours = intval($milTimeHours - 12);
}
else
{
$standardTimeHours = intval($milTimeHours);
}
}
elseif (intval($milTimeHours == 0))
{
$standardTimeHours = 12;
}
else
{
$standardTimeHours = intval($milTimeHours);
}
$standardTime = "$standardTimeHours:$milTimeMinutes $amPm";
return $standardTime;
} 

 

I just need to loop through the database and apply the function.

 

thanks

[PFMaBiSmAd]

A) Store the values as a TIME data type (you can use the mysql STR_TO_DATE() function to covert your existing values).

 

B) You can retrieve TIME data type values in any format you want using the mysql TIME_FORMAT() function.

 

A -

 

STR_TO_DATE(your_existing_time_column,'%H%i')

 

B -

 

TIME_FORMAT(a_TIME_type_column,'%l:%i %p')

[430 Man]

Again, that won't work...  For the legacy code I have to have the time in its own column human readable.

 

Writing one loop to update one column will be exponentially easier than changing the legacy code.

 

but thanks

[mikosiko]

one option:

 

- Create your function in the DB  (small mods necessary)  with CREATE FUNCTION

- Update your table with

  UPDATE <table> SET hrtime = miltostandard(<your time field>);

 

done...

[kickstart]

Hi

 

Possibly a bit OTT.

 

UPDATE timetable
SET hrtime = if(timefield >= 1200,
CONCAT(FLOOR((timefield-1200)/100),':',SUBSTR(CONCAT('00',cast(timefield - (FLOOR((timefield)/100))*100 AS CHAR)),-2),' pm'),
CONCAT(FLOOR((timefield)/100),':',SUBSTR(CONCAT('00',CAST(timefield - (FLOOR((timefield)/100))*100 AS CHAR)),-2),' am'))

 

All the best

 

Keith

[mikosiko]

same result... only difference that having the function in the DB allow you to use it any time you want without write the same over and over... just a different approach 

[requinix]

Can you change the table that the code reads from? And without having to change the table it writes to as well?

Or simply modify the SELECT query?

[PFMaBiSmAd]

UPDATE your_table SET hrtime = TIME_FORMAT(STR_TO_DATE(your_existing_time_column,'%H%i'),'%l:%i %p')

[430 Man]

Can you change the table that the code reads from? And without having to change the table it writes to as well?

Or simply modify the SELECT query?

 

No, the problem is we have one legacy system... then we have a new reporting tool. The reporting tool simply dumps the content of the field... it's a reporting tool not a data manipulation tool... it just reports what's in the db, it does not change it.

 

So it reports the time, but it is in mil time. After talking to the developer of the reporting tool, changing it is monumental. The easiest route is to make another hrtime field and store human readable text there. 

 

So I have the function to convert it, I just need to read one field, convert it and store the result another field....

 

I thought my way was pretty simple but apparently not. I'll look at the stuff above and see if it help.

[430 Man]

Hi

 

Possibly a bit OTT.

 

UPDATE timetable
SET hrtime = if(timefield >= 1200,
CONCAT(FLOOR((timefield-1200)/100),':',SUBSTR(CONCAT('00',cast(timefield - (FLOOR((timefield)/100))*100 AS CHAR)),-2),' pm'),
CONCAT(FLOOR((timefield)/100),':',SUBSTR(CONCAT('00',CAST(timefield - (FLOOR((timefield)/100))*100 AS CHAR)),-2),' am'))

 

All the best

 

Keith

 

This worked fantastically well. I'd love to learn how to make it a function in the db because that looks ubber cool... but for now this saved the day. Gracias Kickstart.

[PFMaBiSmAd]

The built-in date/time functions can already do that.

[430 Man]

The built-in date/time functions can already do that.

 

Indeed! I started with Kickstart's solution because it was on top and I was working my way down.

I see what you are saying, your way is cleaner.  Well, I wrote it in php and called it before the report runs and it works fine... phpmyadim says it took 0.027 second to change the whole db so I'm just going to leave it, but your way is "more betterer." Thanks

[kickstart]

Hi

 

Yep, PFMaBiSmAd solution was far cleaner. Mine worked but my thinking got locked into doing it a long winded way.

 

All the best

 

Keith