I am using pgplsql on Windows 2003, PostgreSQL 8.0.

I am trying to do some simple date arithmetic. All I wanted to do is take a
timestamp
(example: 2005-08-08 16:15:30) without a timezone, and substract a computed
number
of minutes and seconds (such as 310 seconds) from it; keep running into
problems.

Have tried using age(), INTERVAL, and other different approaches. Does
anybody
have a clue how to make this work simply?

Search Discussions

  • John DeSoi at Aug 8, 2005 at 8:45 pm

    On Aug 8, 2005, at 4:23 PM, Lane Van Ingen wrote:

    I am trying to do some simple date arithmetic. All I wanted to do
    is take a
    timestamp
    (example: 2005-08-08 16:15:30) without a timezone, and substract a
    computed
    number
    of minutes and seconds (such as 310 seconds) from it; keep running
    into
    problems.

    select '2005-08-08 16:15:30'::timestamp - '310 seconds'::interval;
    ?column?
    ---------------------
    2005-08-08 16:10:20
    (1 row)



    John DeSoi, Ph.D.
    http://pgedit.com/
    Power Tools for PostgreSQL
  • Tom Lane at Aug 8, 2005 at 9:09 pm

    John DeSoi writes:
    On Aug 8, 2005, at 4:23 PM, Lane Van Ingen wrote:
    I am trying to do some simple date arithmetic.
    select '2005-08-08 16:15:30'::timestamp - '310 seconds'::interval;
    I think the "computed" part is the bit that's missing. Maybe he wants

    select '2005-08-08 16:15:30'::timestamp - (expression) * '1 sec'::interval;

    This should work for pretty much any numeric expression.

    regards, tom lane
  • Lane Van Ingen at Aug 9, 2005 at 12:42 am
    I am closer, but still not there. By computed, I mean that the timestamp
    and the interval (shown below as '2005-08-08 16:15:30' and '310 seconds'
    need to be computed from PL/pgSQL functions and variables in a function
    named getNeighborState(integer).

    The fields I am using are:

    DECLARE
    work_timestamp timestamp without time zone; (the '2005-08-08 16:15:30')
    neighbor_seconds integer; (contains the '310' [seconds]

    My code says:
    select date_trunc('seconds',localtimestamp)::timestamp -
    neighbor_seconds::integer;

    Looks like it wants me to cast, but not sure how to cast this:
    ERROR: operator does not exist: timestamp without time zone - integer
    HINT: No operator matches the given name and argument type(s). You may need
    to add explicit type casts.
    CONTEXT: SQL statement "SELECT
    date_trunc('seconds',localtimestamp)::timestamp - $1 ::integer"

    Haven't done casts before; if that is what is needed, still not sure how to
    create the cast properly from the instructions in the Reference Manual.

    -----Original Message-----
    From: Tom Lane
    Sent: Monday, August 08, 2005 5:09 PM
    To: John DeSoi
    Cc: Lane Van Ingen; pgsql-novice@postgresql.org
    Subject: Re: [NOVICE] Date Arithmetic in PL/pgSql


    John DeSoi <desoi@pgedit.com> writes:
    On Aug 8, 2005, at 4:23 PM, Lane Van Ingen wrote:
    I am trying to do some simple date arithmetic.
    select '2005-08-08 16:15:30'::timestamp - '310 seconds'::interval;
    I think the "computed" part is the bit that's missing. Maybe he wants

    select '2005-08-08 16:15:30'::timestamp - (expression) * '1 sec'::interval;

    This should work for pretty much any numeric expression.

    regards, tom lane
  • Tom Lane at Aug 9, 2005 at 1:19 am

    "Lane Van Ingen" <lvaningen@esncc.com> writes:
    My code says:
    select date_trunc('seconds',localtimestamp)::timestamp -
    neighbor_seconds::integer;
    ERROR: operator does not exist: timestamp without time zone - integer
    Right. What you need is to use the operators that are there, which
    are timestamp minus interval and number times interval:

    select date_trunc('seconds',localtimestamp) - neighbor_seconds * '1 second'::interval;

    The two casts you did write are both pointless, as the given values were
    already of those datatypes.

    regards, tom lane
  • Operationsengineer1 at Aug 31, 2005 at 7:33 pm
    hi all,

    i want to pull all the events that occurred from
    (today - 2 hours) forward (all events during todays
    current date and the last 2 hours from yesterday).

    i tried to hack together some mailing list information
    to this application and came up with this...

    WHERE t_inspect.inspect_timestamp::date >
    (current_date::timestamp - 720 'sec'::interval)

    inspect_timestamp is datatype timestamp. i probably
    shouldn't cast it to date, now that i think about
    it... but i bet that won't make the query run right
    all by itself.

    i'm here so you can bet it didn't work. ;-)

    any help w/b much appreciated.

    tia...





    ____________________________________________________
    Start your day with Yahoo! - make it your home page
    http://www.yahoo.com/r/hs
  • Michael Glaesemann at Aug 31, 2005 at 9:55 pm

    On Sep 1, 2005, at 4:33 AM, wrote:

    WHERE t_inspect.inspect_timestamp::date >
    (current_date::timestamp - 720 'sec'::interval)
    What error did you get? Also, it's always helpful to provide a small,
    self-contained test case so others may try exactly what you have done.

    Looking at it quickly, I'd say you want '720 sec'::interval or 720 *
    '1 sec'::interval. On v8.0.3:

    test=# select 720 'sec'::interval;
    ERROR: syntax error at or near "'sec'" at character 12
    LINE 1: select 720 'sec'::interval;
    ^
    test=# select '720 sec'::interval;
    interval
    ----------
    00:12:00
    (1 row)
    inspect_timestamp is datatype timestamp. i probably
    shouldn't cast it to date, now that i think about
    it... but i bet that won't make the query run right
    all by itself.
    Don't bet :) Try it and see!
    i want to pull all the events that occurred from
    (today - 2 hours) forward (all events during todays
    current date and the last 2 hours from yesterday).
    (As an aside, you can see that 720 seconds is not two hours. I think
    you mean 7200 seconds.)

    Does this help?

    Michael Glaesemann
    grzm myrealbox com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedAug 8, '05 at 8:26p
activeAug 31, '05 at 9:55p
posts7
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase