| Ian Burrell 2005-07-01, 8:24 pm |
| There is a bug in the sample view in the psql_replication_che
ck.pl.=20
It calculates the age incorrectly, returning the minutes part of the
interval instead of total number of minutes.
The sample is:
CREATE VIEW replication_status AS
SELECT customer_name AS object_name,=20
transaction_date,=20
date_part('minutes':
:text, now() - transaction_date) AS age
FROM customer_orders
ORDER BY id DESC
LIMIT 1;
SELECT '2005-07-01 07:20:00'::timestamp
- '2005-07-01 04:10'
?column?
----------
03:10:00
SELECT date_part('minutes',
'2005-07-01 07:20:00'::timestamp
-
'2005-07-01 04:10');
date_part
-----------
10
Instead of returning the total length (190 minutes), it gives the 10
minutes of 3 hours 10 minutes.
The right solution is use extract the epoch, the length in seconds,
and divide by 60.
SELECT date_part('epoch', '2005-07-01 07:20:00'::timestamp
-
'2005-07-01 04:10') / 60 ;
?column?
----------
190
For the example it shoud be:
date_part('epoch'::t
ext, now() - transaction_date) / 60 AS age
- Ian
|