Thread: Date Arithmetic in PL/pgSql
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?
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
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
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 [mailto:tgl@sss.pgh.pa.us]
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
"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
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
On Sep 1, 2005, at 4:33 AM, <operationsengineer1@yahoo.com> 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
--- Michael Glaesemann <grzm@myrealbox.com> wrote: > > On Sep 1, 2005, at 4:33 AM, > <operationsengineer1@yahoo.com> 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 this did the trick! t_inspect.inspect_timestamp > (current_date - 1 * '2 hr'::interval) the only thing to remember is that current_date starts at the very beginning of the current_date (it is day, not time). current_date - 12 hrs = noon yesterday, regardless of the time it is today (it took me a minute to figure that out even though it is pretty obvious when one thinks it over). thanks for the guidance - you helped me get through one problem... now i have only infinity - 1 problems to work through... -lol- ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs