Geek Answers Handbook

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

I save some time values ​​in sqlite in the format of the% H:% M string (for example, "15:43"), but I would like to get them 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 the application code?

+4
source share
6 answers

If you do not extend sqlite with your own custom function , you will need to do this code.

The sqlite strftime date formatting function only supports a small subset of its C instance, insufficient for your problem. sqlite also lacks a select construct such as IF or CASE, which makes it simple if / not possible.

+4
source

Some pseudo codes to help you with this:

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 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 quite sure that all this is SQLite syntax, but you can fix the errors.

+4
source

There are several special situations here. I use "now" as the source, but you can configure it for your line:

 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;
+2
source

Do it in your application. Store it in the usual 24-hour format in a database. In the database, it can be saved as a date record instead of a string (correct me if I am wrong)

+1
source

As PoweRoy recommends, this applies to the application.

It is recommended that any data stored in messages use a standard, language-independent format: http://www.mihai-nita.net/article.php?artID=20051025a

0
source

Here is a worker. Thanks Tomas

 SELECT PatientName, CASE WHEN StrFTime('%H', AppointmentTime) % 12 = 0 THEN 12 ELSE StrFTime('%H', AppointmentTime) % 12 END || ':' || StrFTime('%M', AppointmentTime) || ' ' || CASE WHEN StrFTime('%H', AppointmentTime) > 12 THEN 'PM' ELSE 'AM' END `APP_TIME` From Patients;

OUTPUT
Abdul Salim, 12:05 pm

0
source

More articles:


All Articles