Convert a list in day,H-M-S to Hour format in postgresql

Question

Convert my entire list which is in days , hours-min-sec FORMAT TO only hours which is a whole number because I need to calculate the median of the new list.

For each product id the number of hours is calculated. I have only pasted a bit of my list. but the list is very huge so I cannot hard code the dates and find the hours it takes. Instead want to convert my whole list to hours since I need to calculate median of the new list. Median only takes one format in numbers.

SELECT 
    "FulfillmentHistories"."fulfillmentId", 
    (max("createdAt")-min("createdAt")) as "Range" 
FROM 
    "FulfillmentHistories" //takes the time difference between two dates
where 
    "currentState" IN ('created','delivered')
GROUP BY 
    "FulfillmentHistories"."fulfillmentId" 
HAVING COUNT("FulfillmentHistories"."createdAt")>1
ORDER BY "Range" DESC

This gives me difference in time between two datestamps so the result of this gives a list in day, hour-min-sec format. as shown Need a one format list in postgresql. Please help, thankss!

    Range   

7 days, 4:30:56.324000

2 days, 18:26:25.955000

2 days, 16:38:25.600000

2 days, 15:01:34.488000

2 days, 11:37:27.956000

2 days, 8:15:20.769000

2 days, 6:59:28.396000

2 days, 5:13:47.411000

So you for data `7 days, 4:30:56.324000` you just need `4`, and for the data `2 days, 18:26:25.955000` you just need `18`, am I right ? — Shawn.X, Jun 13 '19 at 09:39
no for instance my first product took 7 days, 4:30mins.. I need to convert the days to hours +the hours it took. Like the total hours for the product to be fulfilled — Kaushika Potluri, Jun 13 '19 at 09:41
So, for data `7 days, 4:30:56.324000`, you want get `7*24 + 4 = 172` ? — Shawn.X, Jun 13 '19 at 09:43
Table fulfillmentId : c9c56d19-2875-4254-977c-56ea8f5a4288 Range : 7 days, 4:30:56.324000 (this is the amount of time for the product to be fulfilled. so i need to take the median of my final list so need to convert the entire time taken for the product to be fulfilled in one format because median its in a varied format median cannot be calculated — Kaushika Potluri, Jun 13 '19 at 09:44
Do you need the Conversion IN your database or in your code, where you get this information? — LoaStaub, Jun 13 '19 at 09:45
Please give a clear example about what result you want, for example , for data `7 days, 4:30:56.324000` , what output data you want? — Shawn.X, Jun 13 '19 at 09:49
the number of hours in 7 days,4:30 which is 7x24+4hrs like that — Kaushika Potluri, Jun 13 '19 at 09:55
I need the conversion in my code. Just need an output list of fully hours or mins just one format so its an whole number list where I CAN CALCULATE THE MEDIAN — Kaushika Potluri, Jun 13 '19 at 09:56
why you dont Convert the Result from your Query into the Format that you want in your code? If you use C# you can save the DataBase output into a "DateTime" and then you convert it into a String like date.ToString(" DateFormat here "); OR if you need just the minutes/hours let them count into Ints (or doubles) You do a timespan between example 00:00 and your value and let them count with TimeSpan.TotalMinutes — LoaStaub, Jun 13 '19 at 10:05
I am gonna have to do this in postgresql. I tried all my ways I just couldnt get it! — Kaushika Potluri, Jun 13 '19 at 10:14
Maybe, if nobody answers try the [IRC Chat](https://www.postgresql.org/community/irc/) from PostgreSQL, they helped me everytime i needed it. — LoaStaub, Jun 13 '19 at 10:32

score 0 · Accepted Answer · answered Jun 13 '19 at 11:20

OK, just as below:

postgres=# select * from test_table ;
          range          
-------------------------
 7 days, 4:30:56.324000
 2 days, 18:26:25.955000
 2 days, 16:38:25.600000
 2 days, 15:01:34.488000
 2 days, 11:37:27.956000
 2 days, 8:15:20.769000
 2 days, 6:59:28.396000
 2 days, 5:13:47.411000
(8 rows)

postgres=# select                    
    range, 
    extract(hour from replace((string_to_array(range,','))[2],' ','')::time) + replace((string_to_array(range,','))[1],' days','')::int * 24 as total_hours
from
    test_table;
          range          | total_hours 
-------------------------+-------------
 7 days, 4:30:56.324000  |         172
 2 days, 18:26:25.955000 |          66
 2 days, 16:38:25.600000 |          64
 2 days, 15:01:34.488000 |          63
 2 days, 11:37:27.956000 |          59
 2 days, 8:15:20.769000  |          56
 2 days, 6:59:28.396000  |          54
 2 days, 5:13:47.411000  |          53
(8 rows)

Error running query: near "from": syntax error ^thats what it says! — Kaushika Potluri, Jun 13 '19 at 11:43
Your SQL maybe write wrong, just check, I have tested my SQL in my local PostgreSQL, it works well. And my sql is just an example to clarify how to solve your problem, you can impove it to meet your needs. — Shawn.X, Jun 13 '19 at 11:44
@KaushikaPotluri you're welcome :D , just accept my answer please, thanks! — Shawn.X, Jun 13 '19 at 11:54

Convert a list in day,H-M-S to Hour format in postgresql

1 Answers1