MySQL data type TIME problems (surprised?)

ATS16805

i dumped a bunch of generic times from an excel spreadsheet into mysql via a script based on fgetcsv(). everything looked good to go-- until now when i'm trying to use that data.

i'm grabbing it w/ a SELECT query and a FOR loop in which i've taken these raw times for which i need to indicate AM or PM (this wouldn't be an issue at all if i didn't want or need to do the AM/PM thing).

hmm-- as i write this, i'm thinking of a possible work around where i could simply do something like

if ($row = "PM") {

$str .= $('field'.$PM); 
} else {
$str .= $('field'.$AM);

or something to that effect.

but, what other options might i have? is there something i could/ should do to my db? some SQL shuffling of the data to somehow get those values to be what they need to be at their residence in the mysql tables? the Excel CSV file doesn't indicate AM or PM, by the way. but, i know which is which because it just happens that i am familiar w/ my material. had i not known these values from personal experience, then i might be in trouble, but as it stands, i can probably do a workaround and get away with it.

suggestions? do you want or need to see my query / array / strototime() code, etc to further help me out?

thanks!!

bretticus

please show your table structure and more of the php code (query(s) and mysql php function(s)) and also some sample data would be good.

It's always better to have real datetime types stored in your database (obviously you would agree.)

ATS16805

hey, thanks for your willingness to help!

i realize that this is what you might call "going a little overboard", but i was playing w/ some CSS stuff in the meantime, so i figured.. what the heck!

thanks again! since i probably wont have the opportunity to do so again-- i'd like to wish the PHPBuilder.com community a VERY HAPPY NEW YEAR!! may all of your most dear dreams come true, and my your ISP never suspend your service, may your database never be hacked... may your...

ding, squeek, squawk, hisss, ding, ding...
(sound of noise-makers being rung in celebration!)

Roger_Ramjet

date format with the %r switch will return the time on a 12 hour clock with AM or PM after it.

$sql = "SELECT date_format(rt02, '%r') AS rt02 FROM w_timetable"

// would return 07:14:00 AM


$sql = "SELECT date_format(rt02, '%l %i %p') AS rt02 FROM w_timetable"

// would return 7 14 AM

$sql = "SELECT concat(date_format(rt02, '%l'), ':', date_format(rt02, '%i %p') AS rt02 FROM w_timetable"

// would return 7:14 AM

ATS16805

hey, Roger, THANK YOU for your reply! it's very helpful!

i actually NEVER saw this reply until now. i even re-posted the same inquiry when i recently resumed work on this project after a little vacation!

i'm going to try this!
however, if you please take a look at the other thread, you'll see that it kinda went in a direction more about whether i was approaching my solution w/ the best possible database structure... but, my original concern was really about this same time format issue...

unfortunately, Thorpe, although graciously willing to help, later reported that he was unable to do so due to a serious server-rebuilding issue on his end..., so long-story short-- i'm basically still stuck-- and now concerned that perhaps i need to restructure my database! i appreciate any advice on this issue. thanks!