Different syntax to add/substract interval

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP












4















Till now I used the following syntax to add an interval to a timestamp:



select now() + '5 year';


This worked fine till I tried to subtract the interval which results in a syntax error.



invalid input syntax for type timestamp with time zone: "5 year"
LINE 1: select now() - '5 year'


In the documentation I learned that the syntax actually is:



select now() - interval '5 year'


So my questions are:
Why does select now() + '5 year' work at all?
Does it work only by accident and it might break in a future Postgresql release?










share|improve this question
























  • I am not sure if this question is on-topic as "Basic SQL" questions are off-topic. But I am not sure if this is still "Basic SQL"...

    – gillesB
    Jan 23 at 14:07







  • 2





    100 % OT, let me assure you. Not as trivial as it may seem.

    – Erwin Brandstetter
    Jan 23 at 19:10











  • @ErwinBrandstetter does "OT" stand for "On Topic" or "Off Topic"?

    – Brad
    Jan 23 at 23:13











  • @Brad: Sorry for the ambiguity. On Topic.

    – Erwin Brandstetter
    Jan 23 at 23:15















4















Till now I used the following syntax to add an interval to a timestamp:



select now() + '5 year';


This worked fine till I tried to subtract the interval which results in a syntax error.



invalid input syntax for type timestamp with time zone: "5 year"
LINE 1: select now() - '5 year'


In the documentation I learned that the syntax actually is:



select now() - interval '5 year'


So my questions are:
Why does select now() + '5 year' work at all?
Does it work only by accident and it might break in a future Postgresql release?










share|improve this question
























  • I am not sure if this question is on-topic as "Basic SQL" questions are off-topic. But I am not sure if this is still "Basic SQL"...

    – gillesB
    Jan 23 at 14:07







  • 2





    100 % OT, let me assure you. Not as trivial as it may seem.

    – Erwin Brandstetter
    Jan 23 at 19:10











  • @ErwinBrandstetter does "OT" stand for "On Topic" or "Off Topic"?

    – Brad
    Jan 23 at 23:13











  • @Brad: Sorry for the ambiguity. On Topic.

    – Erwin Brandstetter
    Jan 23 at 23:15













4












4








4








Till now I used the following syntax to add an interval to a timestamp:



select now() + '5 year';


This worked fine till I tried to subtract the interval which results in a syntax error.



invalid input syntax for type timestamp with time zone: "5 year"
LINE 1: select now() - '5 year'


In the documentation I learned that the syntax actually is:



select now() - interval '5 year'


So my questions are:
Why does select now() + '5 year' work at all?
Does it work only by accident and it might break in a future Postgresql release?










share|improve this question
















Till now I used the following syntax to add an interval to a timestamp:



select now() + '5 year';


This worked fine till I tried to subtract the interval which results in a syntax error.



invalid input syntax for type timestamp with time zone: "5 year"
LINE 1: select now() - '5 year'


In the documentation I learned that the syntax actually is:



select now() - interval '5 year'


So my questions are:
Why does select now() + '5 year' work at all?
Does it work only by accident and it might break in a future Postgresql release?







postgresql datatypes timestamp operator






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 23 at 18:21









Erwin Brandstetter

92.1k9176288




92.1k9176288










asked Jan 23 at 14:05









gillesBgillesB

1255




1255












  • I am not sure if this question is on-topic as "Basic SQL" questions are off-topic. But I am not sure if this is still "Basic SQL"...

    – gillesB
    Jan 23 at 14:07







  • 2





    100 % OT, let me assure you. Not as trivial as it may seem.

    – Erwin Brandstetter
    Jan 23 at 19:10











  • @ErwinBrandstetter does "OT" stand for "On Topic" or "Off Topic"?

    – Brad
    Jan 23 at 23:13











  • @Brad: Sorry for the ambiguity. On Topic.

    – Erwin Brandstetter
    Jan 23 at 23:15

















  • I am not sure if this question is on-topic as "Basic SQL" questions are off-topic. But I am not sure if this is still "Basic SQL"...

    – gillesB
    Jan 23 at 14:07







  • 2





    100 % OT, let me assure you. Not as trivial as it may seem.

    – Erwin Brandstetter
    Jan 23 at 19:10











  • @ErwinBrandstetter does "OT" stand for "On Topic" or "Off Topic"?

    – Brad
    Jan 23 at 23:13











  • @Brad: Sorry for the ambiguity. On Topic.

    – Erwin Brandstetter
    Jan 23 at 23:15
















I am not sure if this question is on-topic as "Basic SQL" questions are off-topic. But I am not sure if this is still "Basic SQL"...

– gillesB
Jan 23 at 14:07






I am not sure if this question is on-topic as "Basic SQL" questions are off-topic. But I am not sure if this is still "Basic SQL"...

– gillesB
Jan 23 at 14:07





2




2





100 % OT, let me assure you. Not as trivial as it may seem.

– Erwin Brandstetter
Jan 23 at 19:10





100 % OT, let me assure you. Not as trivial as it may seem.

– Erwin Brandstetter
Jan 23 at 19:10













@ErwinBrandstetter does "OT" stand for "On Topic" or "Off Topic"?

– Brad
Jan 23 at 23:13





@ErwinBrandstetter does "OT" stand for "On Topic" or "Off Topic"?

– Brad
Jan 23 at 23:13













@Brad: Sorry for the ambiguity. On Topic.

– Erwin Brandstetter
Jan 23 at 23:15





@Brad: Sorry for the ambiguity. On Topic.

– Erwin Brandstetter
Jan 23 at 23:15










2 Answers
2






active

oldest

votes


















3














Like @a_horse explained, there are two operators available for the expression now() - '5 year':




  • now() returns timestamp with time zone (timestamptz).


  • '5 year' is an untyped string literal.



SELECT oprleft::regtype, oprname, oprright::regtype
FROM pg_operator
WHERE oprname = '-'
AND oprleft = 'timestamptz'::regtype;

oprleft | oprname | oprright
--------------------------+---------+--------------------------
timestamp with time zone | - | timestamp with time zone
timestamp with time zone | - | interval
(2 rows)


The exact reason for the choice can be found in the manual in the chapter Operator Type Resolution:




[...]



 2. Check for an operator accepting exactly the input argument types. If one exists (there can be only one exact match in the set of
operators considered), use it. [...]



    a. If one argument of a binary operator invocation is of the unknown type, then assume it is the same type as the other argument for this check. [...]



[...]




Bold emphasis mine. Read the whole chapter to understand the process fully.



The same type is preferred if one argument type is unknown and a matching operator is available. There is an operator for timestamptz - timestamptz, bingo. The operator is resolved here. Fortunately, '5 years' is illegal input for timestamptz, else this might result in confusion!



The operator resolves to timestamptz - interval after adding an explicit type cast:



now() - interval '5 year' -- always the way to go





share|improve this answer






























    4














    My guess(!) is:



    The + operator for timestamps only supports adding an interval (timestamp + interval). And thus it's clear that the string value '5 year' needs to be (implicitly) converted to an interval



    The - operator on the other hand supports two different combinations:




    • timestamp - timestamp


    • timestamp - interval.

    Apparently Postgres prefers to use the timestamp - timestamp option and tries to (implicitly) convert '5 year' to a timestamp which of course fails.






    share|improve this answer

























    • I thought this was due to the fact that timestamptz (not timestamp) is the preferred type among "Date/time types" in Postgres - and commented as much. But on a second look, there seems to be a different explanation. And interval is not a "Date/time type", but a "Timespan type" in the Postgres type system. (Your answer is still basically right.)

      – Erwin Brandstetter
      Jan 23 at 19:15











    Your Answer








    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "182"
    ;
    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: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    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%2fdba.stackexchange.com%2fquestions%2f227871%2fdifferent-syntax-to-add-substract-interval%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    3














    Like @a_horse explained, there are two operators available for the expression now() - '5 year':




    • now() returns timestamp with time zone (timestamptz).


    • '5 year' is an untyped string literal.



    SELECT oprleft::regtype, oprname, oprright::regtype
    FROM pg_operator
    WHERE oprname = '-'
    AND oprleft = 'timestamptz'::regtype;

    oprleft | oprname | oprright
    --------------------------+---------+--------------------------
    timestamp with time zone | - | timestamp with time zone
    timestamp with time zone | - | interval
    (2 rows)


    The exact reason for the choice can be found in the manual in the chapter Operator Type Resolution:




    [...]



     2. Check for an operator accepting exactly the input argument types. If one exists (there can be only one exact match in the set of
    operators considered), use it. [...]



        a. If one argument of a binary operator invocation is of the unknown type, then assume it is the same type as the other argument for this check. [...]



    [...]




    Bold emphasis mine. Read the whole chapter to understand the process fully.



    The same type is preferred if one argument type is unknown and a matching operator is available. There is an operator for timestamptz - timestamptz, bingo. The operator is resolved here. Fortunately, '5 years' is illegal input for timestamptz, else this might result in confusion!



    The operator resolves to timestamptz - interval after adding an explicit type cast:



    now() - interval '5 year' -- always the way to go





    share|improve this answer



























      3














      Like @a_horse explained, there are two operators available for the expression now() - '5 year':




      • now() returns timestamp with time zone (timestamptz).


      • '5 year' is an untyped string literal.



      SELECT oprleft::regtype, oprname, oprright::regtype
      FROM pg_operator
      WHERE oprname = '-'
      AND oprleft = 'timestamptz'::regtype;

      oprleft | oprname | oprright
      --------------------------+---------+--------------------------
      timestamp with time zone | - | timestamp with time zone
      timestamp with time zone | - | interval
      (2 rows)


      The exact reason for the choice can be found in the manual in the chapter Operator Type Resolution:




      [...]



       2. Check for an operator accepting exactly the input argument types. If one exists (there can be only one exact match in the set of
      operators considered), use it. [...]



          a. If one argument of a binary operator invocation is of the unknown type, then assume it is the same type as the other argument for this check. [...]



      [...]




      Bold emphasis mine. Read the whole chapter to understand the process fully.



      The same type is preferred if one argument type is unknown and a matching operator is available. There is an operator for timestamptz - timestamptz, bingo. The operator is resolved here. Fortunately, '5 years' is illegal input for timestamptz, else this might result in confusion!



      The operator resolves to timestamptz - interval after adding an explicit type cast:



      now() - interval '5 year' -- always the way to go





      share|improve this answer

























        3












        3








        3







        Like @a_horse explained, there are two operators available for the expression now() - '5 year':




        • now() returns timestamp with time zone (timestamptz).


        • '5 year' is an untyped string literal.



        SELECT oprleft::regtype, oprname, oprright::regtype
        FROM pg_operator
        WHERE oprname = '-'
        AND oprleft = 'timestamptz'::regtype;

        oprleft | oprname | oprright
        --------------------------+---------+--------------------------
        timestamp with time zone | - | timestamp with time zone
        timestamp with time zone | - | interval
        (2 rows)


        The exact reason for the choice can be found in the manual in the chapter Operator Type Resolution:




        [...]



         2. Check for an operator accepting exactly the input argument types. If one exists (there can be only one exact match in the set of
        operators considered), use it. [...]



            a. If one argument of a binary operator invocation is of the unknown type, then assume it is the same type as the other argument for this check. [...]



        [...]




        Bold emphasis mine. Read the whole chapter to understand the process fully.



        The same type is preferred if one argument type is unknown and a matching operator is available. There is an operator for timestamptz - timestamptz, bingo. The operator is resolved here. Fortunately, '5 years' is illegal input for timestamptz, else this might result in confusion!



        The operator resolves to timestamptz - interval after adding an explicit type cast:



        now() - interval '5 year' -- always the way to go





        share|improve this answer













        Like @a_horse explained, there are two operators available for the expression now() - '5 year':




        • now() returns timestamp with time zone (timestamptz).


        • '5 year' is an untyped string literal.



        SELECT oprleft::regtype, oprname, oprright::regtype
        FROM pg_operator
        WHERE oprname = '-'
        AND oprleft = 'timestamptz'::regtype;

        oprleft | oprname | oprright
        --------------------------+---------+--------------------------
        timestamp with time zone | - | timestamp with time zone
        timestamp with time zone | - | interval
        (2 rows)


        The exact reason for the choice can be found in the manual in the chapter Operator Type Resolution:




        [...]



         2. Check for an operator accepting exactly the input argument types. If one exists (there can be only one exact match in the set of
        operators considered), use it. [...]



            a. If one argument of a binary operator invocation is of the unknown type, then assume it is the same type as the other argument for this check. [...]



        [...]




        Bold emphasis mine. Read the whole chapter to understand the process fully.



        The same type is preferred if one argument type is unknown and a matching operator is available. There is an operator for timestamptz - timestamptz, bingo. The operator is resolved here. Fortunately, '5 years' is illegal input for timestamptz, else this might result in confusion!



        The operator resolves to timestamptz - interval after adding an explicit type cast:



        now() - interval '5 year' -- always the way to go






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jan 23 at 19:08









        Erwin BrandstetterErwin Brandstetter

        92.1k9176288




        92.1k9176288























            4














            My guess(!) is:



            The + operator for timestamps only supports adding an interval (timestamp + interval). And thus it's clear that the string value '5 year' needs to be (implicitly) converted to an interval



            The - operator on the other hand supports two different combinations:




            • timestamp - timestamp


            • timestamp - interval.

            Apparently Postgres prefers to use the timestamp - timestamp option and tries to (implicitly) convert '5 year' to a timestamp which of course fails.






            share|improve this answer

























            • I thought this was due to the fact that timestamptz (not timestamp) is the preferred type among "Date/time types" in Postgres - and commented as much. But on a second look, there seems to be a different explanation. And interval is not a "Date/time type", but a "Timespan type" in the Postgres type system. (Your answer is still basically right.)

              – Erwin Brandstetter
              Jan 23 at 19:15
















            4














            My guess(!) is:



            The + operator for timestamps only supports adding an interval (timestamp + interval). And thus it's clear that the string value '5 year' needs to be (implicitly) converted to an interval



            The - operator on the other hand supports two different combinations:




            • timestamp - timestamp


            • timestamp - interval.

            Apparently Postgres prefers to use the timestamp - timestamp option and tries to (implicitly) convert '5 year' to a timestamp which of course fails.






            share|improve this answer

























            • I thought this was due to the fact that timestamptz (not timestamp) is the preferred type among "Date/time types" in Postgres - and commented as much. But on a second look, there seems to be a different explanation. And interval is not a "Date/time type", but a "Timespan type" in the Postgres type system. (Your answer is still basically right.)

              – Erwin Brandstetter
              Jan 23 at 19:15














            4












            4








            4







            My guess(!) is:



            The + operator for timestamps only supports adding an interval (timestamp + interval). And thus it's clear that the string value '5 year' needs to be (implicitly) converted to an interval



            The - operator on the other hand supports two different combinations:




            • timestamp - timestamp


            • timestamp - interval.

            Apparently Postgres prefers to use the timestamp - timestamp option and tries to (implicitly) convert '5 year' to a timestamp which of course fails.






            share|improve this answer















            My guess(!) is:



            The + operator for timestamps only supports adding an interval (timestamp + interval). And thus it's clear that the string value '5 year' needs to be (implicitly) converted to an interval



            The - operator on the other hand supports two different combinations:




            • timestamp - timestamp


            • timestamp - interval.

            Apparently Postgres prefers to use the timestamp - timestamp option and tries to (implicitly) convert '5 year' to a timestamp which of course fails.







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Jan 23 at 14:31

























            answered Jan 23 at 14:19









            a_horse_with_no_namea_horse_with_no_name

            39.6k775112




            39.6k775112












            • I thought this was due to the fact that timestamptz (not timestamp) is the preferred type among "Date/time types" in Postgres - and commented as much. But on a second look, there seems to be a different explanation. And interval is not a "Date/time type", but a "Timespan type" in the Postgres type system. (Your answer is still basically right.)

              – Erwin Brandstetter
              Jan 23 at 19:15


















            • I thought this was due to the fact that timestamptz (not timestamp) is the preferred type among "Date/time types" in Postgres - and commented as much. But on a second look, there seems to be a different explanation. And interval is not a "Date/time type", but a "Timespan type" in the Postgres type system. (Your answer is still basically right.)

              – Erwin Brandstetter
              Jan 23 at 19:15

















            I thought this was due to the fact that timestamptz (not timestamp) is the preferred type among "Date/time types" in Postgres - and commented as much. But on a second look, there seems to be a different explanation. And interval is not a "Date/time type", but a "Timespan type" in the Postgres type system. (Your answer is still basically right.)

            – Erwin Brandstetter
            Jan 23 at 19:15






            I thought this was due to the fact that timestamptz (not timestamp) is the preferred type among "Date/time types" in Postgres - and commented as much. But on a second look, there seems to be a different explanation. And interval is not a "Date/time type", but a "Timespan type" in the Postgres type system. (Your answer is still basically right.)

            – Erwin Brandstetter
            Jan 23 at 19:15


















            draft saved

            draft discarded
















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • 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%2fdba.stackexchange.com%2fquestions%2f227871%2fdifferent-syntax-to-add-substract-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

            How to check contact read email or not when send email to Individual?

            Displaying single band from multi-band raster using QGIS

            How many registers does an x86_64 CPU actually have?