#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0

    How to get last month based on a timestamp field in a table?


    I have tried the following PostgreSQL statememnts.

    1. select current_date + cast('1 months' as interval)
    2. select tableA.timestampfield - cast('1 months' as interval)
    from tableA

    The 1st statement worked but 2nd statement did not work. Below is the error message.

    Stuck here so really appreciate your help!

    Thanks,
    David


    Error message:

    04:44:58 [SELECT - 0 row(s), 0.000 secs] [Error Code: 0, SQL State: XX000] ERROR: Interval values with month or year parts are not supported
    Detail:
    -----------------------------------------------
    error: Interval values with month or year parts are not supported
    code: 8001
    context: interval months: "1"
    query: 227509
    location: cg_main.cpp:3082
    process: padbmaster [pid=8297]
    -----------------------------------------------
    ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Try this:
    Code:
    select timestampfield - interval '1' month
    from tablea
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0
    Thanks for reply.

    However, it seems - interval '1' month did not take effect. The query just returned the same value as it of the timestamp field.

    Any solution?

    Thanks,
    David
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Then show us a self-contained test case that proofs your point.

    Because this is definitely working: SQLFiddle Example
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0
    It maybe my mistake that I did not mention that we are actually testing this statement against Redshift in Amazon Web Services not PostgreSQL (though Redshift is mostly supported by PostgreSQL/Oracle). So it could be that "select timestampfield - interval '1' month from tablea" works in PostgreSQL but not Redshift. My appologies for not mentioning earlier.

    An alternative I just found is to use "select add_months(ctimestampfield, -1) from tablea" that works with Redshift. With this I have no headache anymore.

    Thanks for your response!

    David

IMN logo majestic logo threadwatch logo seochat tools logo