0

enter image description here

In PostgreSQL / pgadmin - How do I cast timestamp (HH:MM:SS) as decimal so I can multiply by hourly_rate (XX.YY$) and get total charge_amount?

For the life of me I can't find any reasonably simple solution on Google or this forum.

Can it be that's it's not possible?

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
danb2000
  • 19
  • 3
  • 1
    A Postgres timestamp also has a date component. Can you add sample data to your question? – Tim Biegeleisen Aug 08 '21 at 11:07
  • Are you sure you don't mean a `time` type? Sample data and desired results would clarify. – Gordon Linoff Aug 08 '21 at 12:11
  • Your requirement doesn't make sense, and it sounds like you're confusing the concept of date/time with an interval or duration. – Mark Rotteveel Aug 08 '21 at 12:46
  • I added a screenshot snippet to original post. I need to deduct takeoff time from landing time to get total flight time which I then need to multiply by flight hour cost. I hope this better explain my issue. Thanks – danb2000 Aug 08 '21 at 13:34
  • Please provide data as *text*, never as screenshot. Ideally, start with your Postgres version and table definition (`CREATE TABLE` statement). – Erwin Brandstetter Aug 08 '21 at 20:51
  • first I thank you all for your answers. I should have mentioned that i'm in my first steps both in PostgreSQL AND this forum so I apologize if don'r follow expected conventions. that being said, I created this table using the pgadmin GUI and not by writing a "create table" statement so I can't provide the text staement (unless it's written in the background somewere and i'm not aware of it). I use postgresql 13 and pgadmin 4 v5.2. – danb2000 Aug 09 '21 at 09:38

2 Answers2

1

Assuming you are not crossing Midnight:


select round((extract(epoch from '15:09:00'::time - '11:53:00'::time)/3600)::numeric, 2) AS duration;
 duration 
----------
     3.27


--Generic form 

select round((extract(epoch from lndg_final_time -  tkof_01_time)/3600)::numeric, 2) AS duration;

Personally I would use timestamp with time zone for the fields. Then you have 'anchored' times and less confusion especially if you do end up crossing Midnight and/or DST boundaries. You would still subtract the beginning timestamp from the final timestamp to get an interval to extract the epoch from.

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
0

Convert your HMS into seconds and multiply by the (hourlyrate/3600.0). (not debugged) using EXTRACT.

EXTRACT (EPOCH FROM timecolumn) * hourlyrate * (1.0/3600.0)

If your timecolumn also contains date information this computation will yield absurdly large numbers, so be careful. For best results use the interval data type.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • If you do timestamp subtraction e.g `ts_end - ts_begin` you will get an interval that you can extract the epoch from and avoid the large second values. – Adrian Klaver Aug 08 '21 at 16:43