How to get the absolute value of an interval












10















Consider the following statement:



select interval '-1 hours'


I couldn't figure out how to get the absolute value of an interval, i.e. to toggle or remove the sign if negative. The only thing that came to my mind is the following:



select abs(extract(epoch from interval '-1 hours'))


But I wonder if there is a more elegant way (a way that preserves the interval type)?










share|improve this question





























    10















    Consider the following statement:



    select interval '-1 hours'


    I couldn't figure out how to get the absolute value of an interval, i.e. to toggle or remove the sign if negative. The only thing that came to my mind is the following:



    select abs(extract(epoch from interval '-1 hours'))


    But I wonder if there is a more elegant way (a way that preserves the interval type)?










    share|improve this question



























      10












      10








      10


      2






      Consider the following statement:



      select interval '-1 hours'


      I couldn't figure out how to get the absolute value of an interval, i.e. to toggle or remove the sign if negative. The only thing that came to my mind is the following:



      select abs(extract(epoch from interval '-1 hours'))


      But I wonder if there is a more elegant way (a way that preserves the interval type)?










      share|improve this question
















      Consider the following statement:



      select interval '-1 hours'


      I couldn't figure out how to get the absolute value of an interval, i.e. to toggle or remove the sign if negative. The only thing that came to my mind is the following:



      select abs(extract(epoch from interval '-1 hours'))


      But I wonder if there is a more elegant way (a way that preserves the interval type)?







      postgresql






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 21 '18 at 9:23







      moooeeeep

















      asked Aug 17 '12 at 11:20









      moooeeeepmoooeeeep

      21.4k1073132




      21.4k1073132
























          4 Answers
          4






          active

          oldest

          votes


















          5














          A CASE expression would look more self-explanatory. Example:



          SELECT
          i,
          (CASE WHEN (i < INTERVAL '0') THEN (-i) ELSE i END) AS abs_i
          FROM
          (VALUES
          (INTERVAL '-2 h'),
          (INTERVAL '2 m')
          ) AS foo (i)


          which produces:



               i     |  abs_i
          -----------+----------
          -02:00:00 | 02:00:00
          00:02:00 | 00:02:00





          share|improve this answer































            7














            You can find the greatest value between i and -i. For example:



            SELECT greatest(-'1 hour'::interval, '1 hour'::interval);





            share|improve this answer































              3














              It way isn't more elegant than yours, but it returns an interval type



              select interval '-1 hours'*sign(extract(epoch from interval '-1 hours'))  





              share|improve this answer


























              • you're right, I've corrected my answer

                – khomyakoshka
                Aug 17 '12 at 12:03



















              3














              There's a discussion on the pgsql-general mailing-list: Absolute value of intervals on why a built-in abs(interval) function is not provided with PostgreSQL.

              In short, there's no consensus about what it should do in some cases, when considering the componentized nature of the interval type.



              But anyone can create their function implementing their own idea about what it should compute, for instance, building on the expression from LisMorski's answer:



              CREATE FUNCTION abs(interval) RETURNS interval AS
              $$ select case when ($1<interval '0') then -$1 else $1 end; $$
              LANGUAGE sql immutable;


              Simple SQL functions are generally inlined during query execution, so the performance should be comparable to having the expression inside the query.



              Example:



              #= select abs(interval '-2 days +3 minutes');
              abs
              ------------------
              2 days -00:03:00


              # select abs(now()-clock_timestamp());
              abs
              -----------------
              00:00:00.000146





              share|improve this answer























                Your Answer






                StackExchange.ifUsing("editor", function () {
                StackExchange.using("externalEditor", function () {
                StackExchange.using("snippets", function () {
                StackExchange.snippets.init();
                });
                });
                }, "code-snippets");

                StackExchange.ready(function() {
                var channelOptions = {
                tags: "".split(" "),
                id: "1"
                };
                initTagRenderer("".split(" "), "".split(" "), channelOptions);

                StackExchange.using("externalEditor", function() {
                // Have to fire editor after snippets, if snippets enabled
                if (StackExchange.settings.snippets.snippetsEnabled) {
                StackExchange.using("snippets", function() {
                createEditor();
                });
                }
                else {
                createEditor();
                }
                });

                function createEditor() {
                StackExchange.prepareEditor({
                heartbeatType: 'answer',
                autoActivateHeartbeat: false,
                convertImagesToLinks: true,
                noModals: true,
                showLowRepImageUploadWarning: true,
                reputationToPostImages: 10,
                bindNavPrevention: true,
                postfix: "",
                imageUploader: {
                brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
                contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
                allowUrls: true
                },
                onDemand: true,
                discardSelector: ".discard-answer"
                ,immediatelyShowMarkdownHelp:true
                });


                }
                });














                draft saved

                draft discarded


















                StackExchange.ready(
                function () {
                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f12004806%2fhow-to-get-the-absolute-value-of-an-interval%23new-answer', 'question_page');
                }
                );

                Post as a guest















                Required, but never shown

























                4 Answers
                4






                active

                oldest

                votes








                4 Answers
                4






                active

                oldest

                votes









                active

                oldest

                votes






                active

                oldest

                votes









                5














                A CASE expression would look more self-explanatory. Example:



                SELECT
                i,
                (CASE WHEN (i < INTERVAL '0') THEN (-i) ELSE i END) AS abs_i
                FROM
                (VALUES
                (INTERVAL '-2 h'),
                (INTERVAL '2 m')
                ) AS foo (i)


                which produces:



                     i     |  abs_i
                -----------+----------
                -02:00:00 | 02:00:00
                00:02:00 | 00:02:00





                share|improve this answer




























                  5














                  A CASE expression would look more self-explanatory. Example:



                  SELECT
                  i,
                  (CASE WHEN (i < INTERVAL '0') THEN (-i) ELSE i END) AS abs_i
                  FROM
                  (VALUES
                  (INTERVAL '-2 h'),
                  (INTERVAL '2 m')
                  ) AS foo (i)


                  which produces:



                       i     |  abs_i
                  -----------+----------
                  -02:00:00 | 02:00:00
                  00:02:00 | 00:02:00





                  share|improve this answer


























                    5












                    5








                    5







                    A CASE expression would look more self-explanatory. Example:



                    SELECT
                    i,
                    (CASE WHEN (i < INTERVAL '0') THEN (-i) ELSE i END) AS abs_i
                    FROM
                    (VALUES
                    (INTERVAL '-2 h'),
                    (INTERVAL '2 m')
                    ) AS foo (i)


                    which produces:



                         i     |  abs_i
                    -----------+----------
                    -02:00:00 | 02:00:00
                    00:02:00 | 00:02:00





                    share|improve this answer













                    A CASE expression would look more self-explanatory. Example:



                    SELECT
                    i,
                    (CASE WHEN (i < INTERVAL '0') THEN (-i) ELSE i END) AS abs_i
                    FROM
                    (VALUES
                    (INTERVAL '-2 h'),
                    (INTERVAL '2 m')
                    ) AS foo (i)


                    which produces:



                         i     |  abs_i
                    -----------+----------
                    -02:00:00 | 02:00:00
                    00:02:00 | 00:02:00






                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Aug 17 '12 at 11:44









                    LisMorskiLisMorski

                    30916




                    30916

























                        7














                        You can find the greatest value between i and -i. For example:



                        SELECT greatest(-'1 hour'::interval, '1 hour'::interval);





                        share|improve this answer




























                          7














                          You can find the greatest value between i and -i. For example:



                          SELECT greatest(-'1 hour'::interval, '1 hour'::interval);





                          share|improve this answer


























                            7












                            7








                            7







                            You can find the greatest value between i and -i. For example:



                            SELECT greatest(-'1 hour'::interval, '1 hour'::interval);





                            share|improve this answer













                            You can find the greatest value between i and -i. For example:



                            SELECT greatest(-'1 hour'::interval, '1 hour'::interval);






                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Apr 30 '14 at 1:47









                            Daniel HernándezDaniel Hernández

                            655410




                            655410























                                3














                                It way isn't more elegant than yours, but it returns an interval type



                                select interval '-1 hours'*sign(extract(epoch from interval '-1 hours'))  





                                share|improve this answer


























                                • you're right, I've corrected my answer

                                  – khomyakoshka
                                  Aug 17 '12 at 12:03
















                                3














                                It way isn't more elegant than yours, but it returns an interval type



                                select interval '-1 hours'*sign(extract(epoch from interval '-1 hours'))  





                                share|improve this answer


























                                • you're right, I've corrected my answer

                                  – khomyakoshka
                                  Aug 17 '12 at 12:03














                                3












                                3








                                3







                                It way isn't more elegant than yours, but it returns an interval type



                                select interval '-1 hours'*sign(extract(epoch from interval '-1 hours'))  





                                share|improve this answer















                                It way isn't more elegant than yours, but it returns an interval type



                                select interval '-1 hours'*sign(extract(epoch from interval '-1 hours'))  






                                share|improve this answer














                                share|improve this answer



                                share|improve this answer








                                edited Aug 17 '12 at 12:01

























                                answered Aug 17 '12 at 11:46









                                khomyakoshkakhomyakoshka

                                1,143616




                                1,143616













                                • you're right, I've corrected my answer

                                  – khomyakoshka
                                  Aug 17 '12 at 12:03



















                                • you're right, I've corrected my answer

                                  – khomyakoshka
                                  Aug 17 '12 at 12:03

















                                you're right, I've corrected my answer

                                – khomyakoshka
                                Aug 17 '12 at 12:03





                                you're right, I've corrected my answer

                                – khomyakoshka
                                Aug 17 '12 at 12:03











                                3














                                There's a discussion on the pgsql-general mailing-list: Absolute value of intervals on why a built-in abs(interval) function is not provided with PostgreSQL.

                                In short, there's no consensus about what it should do in some cases, when considering the componentized nature of the interval type.



                                But anyone can create their function implementing their own idea about what it should compute, for instance, building on the expression from LisMorski's answer:



                                CREATE FUNCTION abs(interval) RETURNS interval AS
                                $$ select case when ($1<interval '0') then -$1 else $1 end; $$
                                LANGUAGE sql immutable;


                                Simple SQL functions are generally inlined during query execution, so the performance should be comparable to having the expression inside the query.



                                Example:



                                #= select abs(interval '-2 days +3 minutes');
                                abs
                                ------------------
                                2 days -00:03:00


                                # select abs(now()-clock_timestamp());
                                abs
                                -----------------
                                00:00:00.000146





                                share|improve this answer




























                                  3














                                  There's a discussion on the pgsql-general mailing-list: Absolute value of intervals on why a built-in abs(interval) function is not provided with PostgreSQL.

                                  In short, there's no consensus about what it should do in some cases, when considering the componentized nature of the interval type.



                                  But anyone can create their function implementing their own idea about what it should compute, for instance, building on the expression from LisMorski's answer:



                                  CREATE FUNCTION abs(interval) RETURNS interval AS
                                  $$ select case when ($1<interval '0') then -$1 else $1 end; $$
                                  LANGUAGE sql immutable;


                                  Simple SQL functions are generally inlined during query execution, so the performance should be comparable to having the expression inside the query.



                                  Example:



                                  #= select abs(interval '-2 days +3 minutes');
                                  abs
                                  ------------------
                                  2 days -00:03:00


                                  # select abs(now()-clock_timestamp());
                                  abs
                                  -----------------
                                  00:00:00.000146





                                  share|improve this answer


























                                    3












                                    3








                                    3







                                    There's a discussion on the pgsql-general mailing-list: Absolute value of intervals on why a built-in abs(interval) function is not provided with PostgreSQL.

                                    In short, there's no consensus about what it should do in some cases, when considering the componentized nature of the interval type.



                                    But anyone can create their function implementing their own idea about what it should compute, for instance, building on the expression from LisMorski's answer:



                                    CREATE FUNCTION abs(interval) RETURNS interval AS
                                    $$ select case when ($1<interval '0') then -$1 else $1 end; $$
                                    LANGUAGE sql immutable;


                                    Simple SQL functions are generally inlined during query execution, so the performance should be comparable to having the expression inside the query.



                                    Example:



                                    #= select abs(interval '-2 days +3 minutes');
                                    abs
                                    ------------------
                                    2 days -00:03:00


                                    # select abs(now()-clock_timestamp());
                                    abs
                                    -----------------
                                    00:00:00.000146





                                    share|improve this answer













                                    There's a discussion on the pgsql-general mailing-list: Absolute value of intervals on why a built-in abs(interval) function is not provided with PostgreSQL.

                                    In short, there's no consensus about what it should do in some cases, when considering the componentized nature of the interval type.



                                    But anyone can create their function implementing their own idea about what it should compute, for instance, building on the expression from LisMorski's answer:



                                    CREATE FUNCTION abs(interval) RETURNS interval AS
                                    $$ select case when ($1<interval '0') then -$1 else $1 end; $$
                                    LANGUAGE sql immutable;


                                    Simple SQL functions are generally inlined during query execution, so the performance should be comparable to having the expression inside the query.



                                    Example:



                                    #= select abs(interval '-2 days +3 minutes');
                                    abs
                                    ------------------
                                    2 days -00:03:00


                                    # select abs(now()-clock_timestamp());
                                    abs
                                    -----------------
                                    00:00:00.000146






                                    share|improve this answer












                                    share|improve this answer



                                    share|improve this answer










                                    answered Jun 27 '17 at 19:01









                                    Daniel VéritéDaniel Vérité

                                    39.4k1189107




                                    39.4k1189107






























                                        draft saved

                                        draft discarded




















































                                        Thanks for contributing an answer to Stack Overflow!


                                        • Please be sure to answer the question. Provide details and share your research!

                                        But avoid



                                        • Asking for help, clarification, or responding to other answers.

                                        • Making statements based on opinion; back them up with references or personal experience.


                                        To learn more, see our tips on writing great answers.




                                        draft saved


                                        draft discarded














                                        StackExchange.ready(
                                        function () {
                                        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f12004806%2fhow-to-get-the-absolute-value-of-an-interval%23new-answer', 'question_page');
                                        }
                                        );

                                        Post as a guest















                                        Required, but never shown





















































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown

































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown







                                        Popular posts from this blog

                                        Guess what letter conforming each word

                                        Port of Spain

                                        Run scheduled task as local user group (not BUILTIN)