postgresql - Convert string to time

date, postgresql, sql, time

Solution

time data type is just time - not a format. to get time with wanted format use `to_char`, eg fro your `140159`:

t=# select to_char('140159'::time,'HH:MI AM');
 to_char
----------
 02:01 PM
(1 row)

Mind I first cast as time and only then format it

Problem

I have column where I saved the transaction time, format is HHMMSS for example: ``` 140159 013115 235900 ``` then I want to convert those time to HH:MM AM/PM so results would be: ``` 2:01 PM 1:31 AM 11:59 PM ``` Here are the queries ive tried, but none of them return the results I want.. ``` SELECT TO_CHAR(TO_TIMESTAMP(TRANSTIME,'hh24:mi:ss AM'),'hh12:mi:ss AM') FROM PRODUCTSALES order by TRANSTIME desc LIMIT 100 SELECT TO_TIMESTAMP(TRANSTIME, 'HH24:MI')::TIME ```

Original source