On Fri, Jan 13, 2023 at 4:57 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > Given what extract() provides, > > stored months = years * 12 + months > > stored days = days > > stored usec = reconstruct from hours+minutes+seconds+microseconds > > Perhaps it wouldn't be a bad idea to provide a couple more extract() > keywords to make that easier. > > 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 Cheers, Ken -- AGENCY Software A Free Software data system By and for non-profits *http://agency-software.org/ <http://agency-software.org/>* *https://demo.agency-software.org/client <https://demo.agency-software.org/client>* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.