postgres decimal point caliculation

Question

Here I have this query to have sums for resource usages per hour.

SUM (CEIL(EXTRACT(EPOCH FROM (ended_at - started_at)/60/60))*price_hourly)

I calculate with having UNIX time between eneded_at & started_at timestamp and use ceil function returns the smallest integer value, and then multiply by price_hourly. So that I can count operating time even it is so small.

But I found out if timestamp is milliseconds like 2016-03-09 20:20:0.000010 This doesn’t behave like what I expected.

ended_at = 2016-03-09 20:20:00.000000
started_at = 2016-03-09 20:20:0.000001
price_hourly = 10

comes out 0 ( expected 10 )

Timestamp has some limitation to calculate such a small digits like this? Maybe this part ( 0.000001 / 60 /60 ) drops the last .1

Do you have any idea to get the last .1 and get digits correctly? or Do you know more nice way to do the exact same thing?

Kristján · Accepted Answer · 2016-03-10 04:59:24Z

1

You're dividing too early. The Postgres wiki says dividing intervals is under discussion, and if you want to do it, you should convert to seconds first.

#= SELECT ('2016-03-09 20:20:0.000001'::timestamp - '2016-03-09 20:20:00.000000'::timestamp) / 60;
 ?column?
----------
 00:00:00

#= SELECT EXTRACT(EPOCH FROM '2016-03-09 20:20:0.000001'::timestamp - '2016-03-09 20:20:00.000000'::timestamp) / 60;
       ?column?
----------------------
 1.66666666666667e-08

answered Mar 10, 2016 at 4:59

Kristján

19k5 gold badges55 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres decimal point caliculation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related