Menu
I'm new to PostgreSQL (I have been using MS SQL for many years) and need to convert a numeric column which contains a time in seconds to HH:MM:SS format.
I have Googled and found that to_char(interval '1000s', 'HH24:MI:SS') works so I am attempting to use this with my field name:
to_char(fieldname, 'HH24:MI:SS') gives an error cannot use "S" and "PL"/"MI"/"SG"/"PR" together
to_char(fieldname::interval, 'HH24:MI:SS') gives an error cannot cast type numeric to interval
Can anyone show me where I am going wrong?
450
To do the conversion yourself follow these steps. Find the number of whole hours by dividing the number of seconds by 3,600. The number to the left of the decimal point is the number of whole hours. The number to the right of the decimal point is the number of partial hours.
Discussion: Use the :: operator to convert strings containing numeric values to the DECIMAL data type. In our example, we converted the string ' 5800.79 ' to 5800.79 (a DECIMAL value). This operator is used to convert between different data types.
If your value is a number of seconds, divide the number by 86400.0 (the number of seconds in a day) and then use the value format hh:mm:ss . NOTE that this is like a time, and will show you the 24-hour time modded by day. So if you had 25 hours, it would show 01:00:00 rather than 25:00:00 .
To convert the number of seconds to hh:mm:ss format, please apply the below formula: =TEXT(A1/(24*60*60),"hh:mm:ss") Please try it, hope it can help you！
Use the :: operator to convert strings containing numeric values to the DECIMAL data type. In our example, we converted the string ‘ 5800.79 ’ to 5800.79 (a DECIMAL value). This operator is used to convert between different data types. It’s very popular within PostgreSQL.
In order to get second from Timestamp in postgresql we use Extract Keyword. Within the Extract keyword we have to mention SECOND as we are getting SECOND from timestamp
The Decimal number is the Time component, where 0 is Midnight and 1 the other midnight, thus 0.5 is midday. 1 Second, as a whole number, could be converted to 1 second in DateTime by dividing it by 24 (Hours in Day), dividing again by 60 (Minutes in Hour) and then again by 60 (Seconds in minutes).
SELECT TO_CHAR('1000 second'::interval, 'HH24:MI:SS') or, in your case
SELECT TO_CHAR((mycolumn || ' second')::interval, 'HH24:MI:SS') FROM mytable To convert seconds to HH:MM:SS
SELECT 120 * interval '1 sec'; You will get 00:02:00
To convert minutes to HH:MM:SS
SELECT 120 * interval '1 min'; You will get 02:00:00
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
