x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 446

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
0
daniel_spiri
1 Solution

Commented:
0

Commented:
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

Commented:
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

Author 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

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

Author Commented:
0

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.