> Thanks Tom! That helped me spell it out and understand it a little more > clearly. Both to understand the non-identicalness, and to see the > specifics. But yeah it would be nice if it was a little easier to extract! > :) > > WITH foo AS ( > WITH inters AS ( > SELECT > '1 day 2 hours'::interval AS i1, > '26 hours'::interval AS i2 > ) > SELECT > *, > EXTRACT(YEARS FROM i1)*12 + EXTRACT(MONTHS FROM i1) AS i1_months, > EXTRACT(DAYS FROM i1) AS i1_days, > EXTRACT(HOURS FROM i1) * 60 * 60 * 1000 > + EXTRACT(MINUTES FROM i1) * 60 * 1000 > + EXTRACT(SECONDS FROM i1) * 1000 > + EXTRACT(MICROSECONDS FROM i1) > AS i1_msec, > EXTRACT(YEARS FROM i2)*12 + EXTRACT(MONTHS FROM i2) AS i2_months, > EXTRACT(DAYS FROM i2) AS i2_days, > EXTRACT(HOURS FROM i2) * 60 * 60 * 1000 > + EXTRACT(MINUTES FROM i2) * 60 * 1000 > + EXTRACT(SECONDS FROM i2) * 1000 > + EXTRACT(MICROSECONDS FROM i2) > AS i2_msec, > i1=i2 AS equals > FROM inters > ) > SELECT > *, > (i1_months=i2_months AND i1_days=i2_days AND i1_msec=i2_msec) AS > identical, > i1_months * 30 * 24 * 60 * 60 * 1000 > + i1_days * 24 * 60 * 60 * 1000 > + i1_msec AS i1_msec_total, > i2_months * 30 * 24 * 60 * 60 * 1000 > + i2_days * 24 * 60 * 60 * 1000 > + i2_msec AS i2_msec_total > > FROM foo; > > -[ RECORD 1 ]-+--------------- > i1 | 1 day 02:00:00 > i2 | 26:00:00 > i1_months | 0 > i1_days | 1 > i1_msec | 7200000 > i2_months | 0 > i2_days | 0 > i2_msec | 93600000 > equals | t > identical | f > i1_msec_total | 93600000 > i2_msec_total | 93600000 I am not sure if I fully understand what you want to do here but I guess you can extract "93600000" part easier using "EPOCH" of EXTRACT function. SELECT EXTRACT(EPOCH FROM i1) AS epoch_i1, EXTRACT(EPOCH FROM i2) AS epoch_i2 FROM ( SELECT '1 day 2 hours'::interval AS i1, '26 hours'::interval AS i2) AS s; epoch_i1 | epoch_i2 --------------+-------------- 93600.000000 | 93600.000000 (1 row) Best reagards, -- Tatsuo Ishii SRA OSS LLC English: http://www.sraoss.co.jp/index_en/ Japanese:http://www.sraoss.co.jp
