Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    10

    Unanswered: How can i get Date after adding Interval.. ?

    Hiii

    I want to get proper date after add Interval. I have also try with below query, but i had not got any proper result.

    Query :
    select date '2011-06-30' + interval '6 months';

    If i have run this query i got the below output.
    Output:
    2011-12-30 00:00:00

    Generally if i have add 6 months in '2011-06-30' then correct output expect from query is '2011-06-31'.

    Why it happen here..? How can i get proper output from query.. ?

    -- Hiren Lad..

  2. #2
    Join Date
    Mar 2011
    Posts
    27
    It is automatically changing the output data type to timestamp (see examples here). You can override the cast if you list, e.g.:

    select (date '2011-06-30' + interval '6 months')::date;

  3. #3
    Join Date
    May 2011
    Posts
    10

    How can i get Date after adding Interval.. ?

    Hello someidiot,

    Here some mistake in my post.. i have again mention my question.

    Query :
    select date '2011-06-30' + interval '6 months';

    If i have run this query i got the below output.
    Output:
    2011-12-30 00:00:00

    Generally if i have add 6 months in '2011-06-30' then correct output expect from query is '2011-12-31 00:00:00'.

    Is it any idea ?

    -- Hiren Lad

  4. #4
    Join Date
    Mar 2011
    Posts
    27
    Quote Originally Posted by hirenlad View Post
    Hello someidiot,

    Here some mistake in my post.. i have again mention my question.

    Query :
    select date '2011-06-30' + interval '6 months';

    If i have run this query i got the below output.
    Output:
    2011-12-30 00:00:00

    Generally if i have add 6 months in '2011-06-30' then correct output expect from query is '2011-12-31 00:00:00'.

    Is it any idea ?

    -- Hiren Lad
    Oh sorry, I miss-interpreted the question.

    Why would you expect that result? 6 months has no definite length, it depends which months. The doco says: "Note there can be ambiguity in the months returned by age because different months have a different number of days. PostgreSQL's approach uses the month from the earlier of the two dates when calculating partial months. For example, age('2004-06-01', '2004-04-30') uses April to yield 1 mon 1 day, while using May would yield 1 mon 2 days because May has 31 days, while April has only 30."

    But that would give 27th Dec using June's 30 days * 6 = 180 days.

    It looks like it used the avg number of days in a month rounded up to 183.

  5. #5
    Join Date
    May 2011
    Posts
    10
    Quote Originally Posted by someidiot View Post
    Oh sorry, I miss-interpreted the question.

    Why would you expect that result? 6 months has no definite length, it depends which months. The doco says: "Note there can be ambiguity in the months returned by age because different months have a different number of days. PostgreSQL's approach uses the month from the earlier of the two dates when calculating partial months. For example, age('2004-06-01', '2004-04-30') uses April to yield 1 mon 1 day, while using May would yield 1 mon 2 days because May has 31 days, while April has only 30."

    But that would give 27th Dec using June's 30 days * 6 = 180 days.

    It looks like it used the avg number of days in a month rounded up to 183.
    I understand what you are saying but we never had this issues when we worked with other RDBMS, SQL Server for instance. Anyways, I think that is irrelevant, coming back to point, I would like to know if there is some way to achieve what I want in Postgre? I want 31st of December if I add 6 months to 30th June.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •