asked on

Time conversion to Metric

Hello Experts!

I am looking for this Postgres function that will take a "timestamp without time zone" value and convert it to metric time.

for example:
time metric time [hh.mm]
19 days 06:17:00 462.28
155:05:00 155.08

borgunit

http://www.bigresource.com/Tracker/Track-vb-NvraFJtHqL/

TommySzalapski

You could extract the epoch (which does second) and convert
EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours')/3600;
This would give hours since 60*60 = 3600

earth man2

do you mean interval instead of timestamp ?
select round(cast ( extract(epoch from interval '19 days 06:17:00')as numeric )/3600.0, 2);

daniel_spiri

ASKER

Earthman2,
When I run the query:

select round(cast ( extract(epoch from interval '19 days 06:17:00')as numeric )/3600.0, 2);

Result: 462.28

The minutes don't seem right.

The query on '19 days 06:01:00' returns 462.02. (but it should be 462.01)

ASKER CERTIFIED SOLUTION

earth man2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

daniel_spiri

ASKER

Also, can you please help me in transforming this into a function?

earth man2

create or replace function interval2dec( interval )returns text as $$
select div(cast (extract( epoch from $1)as integer),3600)::text || ':' || extract( minute from $1);
$$ language SQL;

select interval2dec( '19 days 06:17:00'::interval);