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
daniel_spiriAsked:
Who is Participating?
 
earth man2Connect With a Mentor Commented:
select div( cast (extract ( epoch from interval '19 days 06:17:00')as integer),3600)::text || ':' || extract( minute from interval '19 days 06:17:00');
0
 
TommySzalapskiCommented:
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
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
earth man2Commented:
do you mean interval instead of timestamp ?
select round(cast ( extract(epoch from interval '19 days 06:17:00')as numeric )/3600.0, 2);
0
 
daniel_spiriAuthor Commented:
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)
0
 
daniel_spiriAuthor Commented:
Also, can you please help me in transforming this into a function?
0
 
earth man2Commented:
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);
0
All Courses

From novice to tech pro — start learning today.