Can you format 24-hour time string into 12-hour time string with AM/PM?

Question

I store some time values in sqlite in %H:%M string format (for example "15:43"), but I would like to get them out formatted in 12 hour format with AM/PM indicators (for example "3:43 PM"). Is this possible with sqlite (and if so, how), or do I need to do this in my application code?

Answer 1

Unless you extend sqlite with your own custom function, you'll have to do this is code.

sqlite's strftime date formatting function only supports a small subset of its C counterpart, insufficient for your problem. sqlite also lacks a selection construct like IF or CASE, making simple if/else impossible.

Answer 2

Some pseudo code to help you on the way:

if (hourpart of time >= 12)
    subtract 12 from hours
    append string " pm"
else // hourpart < 12
    append string " am"
end if

In SQL you can accomplish this using the CASE syntax.

---

After taking a closer look at the problem:

SELECT (CASE HOUR(myTimeColumn) >= 12 WHEN 1 THEN
         ((HOUR(myTimeColumn) - 12) + '-' + MINUTE(myTimeColumn) + ' pm')
       ELSE
         (HOUR(myTimeColumn) + '-' + MINUTE(myTimeColumn) + ' am')
       AS AmPmTime,
       someOtherColumn
FROM myTable

I'm not entirely sure that all of that is valid SQLite syntax, but you should be able to correct the bugs.

Answer 3

There are a few special situation that are covered here. I'm using 'now' as a source, but you can adjust it for your string:

select
  CASE 
  --For 00:05, for example.
  WHEN (strftime('%H', 'now', 'localtime') - 12) = -12
  THEN  '12:' || strftime('%M', 'now', 'localtime') ||' '|| 'AM'
  --For 12:05, for example.
  WHEN (strftime('%H', 'now', 'localtime') - 12) = 0
  THEN '12:' || strftime('%M', 'now', 'localtime') ||' '|| 'PM'
  --other am time
  WHEN (strftime('%H', 'now', 'localtime') - 12) < 0
  THEN  strftime('%H', 'now', 'localtime') ||':'||
    strftime('%M', 'now', 'localtime') ||' '|| 'AM'
  ELSE
  --other pm time
    (cast(strftime('%H', 'now', 'localtime') as integer) - 12) ||':'||
    strftime('%M', 'now', 'localtime') ||' '|| 'PM'
  END here_you_go_usa;

Answer 4

Do it in your application. Store it in normal 24h format in the database. In the database it can be stored as a Date entry instead of a string (correct me if im wrong)