Turning TIMESTAMP sql into 12 hour time

Question

Hi, I am writing an app that allows people to schedule things for certain times and then displays them. The time of the event is stored in the database as an sql TIME, because there are things im doing in the DB that need this structure.

However, on the output page i want the time to be in 12:00 am/pm format, but i dont want to run a conversion script every time i step through the database output loop. i dont want this while (database loop){ convert to clean time output event info, with clean time }

cause i think it is too much strain and unneccesary. can anyone think of a way to do this better short of storing the time in the db twice, once in 24 hour and once in 12. any suggestions would be greatly appreciated.

Answer 1

You do not state which DBMS and which platform, but most modern ones would have some sort of CONVERT or CAST function that you could use, either at the DB level, or while processing your output.

Answer 2

Converting times and dates on the database takes very little resource and will be much faster than doing it in PHP or JavaScript. I would use the date and time functions provided by your database e.g. DATE_FORMAT(date,format) in mySql.

Answer 3

for sql server use convert with style 100

select convert(varchar(30),getdate(),100)

Feb 6 2009 1:44PM

Answer 4

The output page is your presentation layer. Don't mix up presentation issues into the database layer. Especially don't force a denormalisation just for presentation's sake.

i dont want to run a conversion script every time i step through the database output loop

Whyever not? Depending on what language you're using it shouldn't be any more work than just sending the time through a strftime() operation before plonking it onto the page. It's not going to be slow.