Search Postgresql Archives

Re: Intervals and ISO 8601 duration

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> 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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux