Date manipulation

On Mon, Apr 19, 2004 at 09:02:35AM +1200, Hadley Willan wrote:

I want to write a function in pgsql that given a number like 7 turns
that into a date, 7 weeks in the past. I have a table that is
essentially logging some information and want to write a trigger that is
data driven in trimming old values out of the log, that way if I thought
that 4 weeks was sufficient, I could adjust the value.

Just use the - operator ...

alvherre=# select '2004-01-01'::date - 7 * '1 week'::interval;
?column?
---------------------
2003-11-13 00:00:00
(1 fila)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Hay que recordar que la existencia en el cosmos, y particularmente la
elaboraci�n de civilizaciones dentre de �l no son, por desgracia,
nada id�licas" (Ijon Tichy)

Thanks, that's pretty easy.

I take it I could just use ''now'' instead of a date

dateRange = ''now''::date - ( 7 * '1 week'::interval );

Hadley

On Mon, 2004-04-19 at 10:36, Alvaro Herrera wrote:

Hadley Willan <hadley.willan@deeperdesign.co.nz> writes:

I take it I could just use ''now'' instead of a date
dateRange = ''now''::date - ( 7 * '1 week'::interval );

You probably want to use the SQL-standard spelling:

dateRange = current_date - ( 7 * '1 week'::interval );

Aside from being standard, this doesn't pose risk of the constant being
reduced sooner than you want. The other way is likely to break if the
plpgsql function gets cached over more than a day.

regards, tom lane

On Mon, Apr 19, 2004 at 11:18:23 +1200,
Hadley Willan <hadley.willan@deeperdesign.co.nz> wrote:

Thanks, that's pretty easy.

I take it I could just use ''now'' instead of a date

dateRange = ''now''::date - ( 7 * '1 week'::interval );

If you actually are using a date you don't have to use an interval as
the number subtracted will be in days. So you could just use
current_date - (7 * 7);

Bruno Wolff III <bruno@wolff.to> writes:

Hadley Willan <hadley.willan@deeperdesign.co.nz> wrote:

dateRange = ''now''::date - ( 7 * '1 week'::interval );

If you actually are using a date you don't have to use an interval as
the number subtracted will be in days. So you could just use
current_date - (7 * 7);

Good point --- in fact, that's probably exactly what Hadley wants,
because "date - integer" will yield a date and not a timestamp. The
form involving interval will do odd things when crossing a DST
transition.

regards, tom lane

Yes, I'm not worried about an exact DST. The date items are stored with
Timestamp Timezone.

Thanks to everybody for your technical tips and help.

Hadley

On Mon, 2004-04-19 at 16:42, Tom Lane wrote:

In article <1082322155.6031.3.camel@atlas.sol.deeper.co.nz>,
Hadley Willan <hadley.willan@deeperdesign.co.nz> writes:

Hi all,
    I want to write a function in pgsql that given a number like 7 turns that
into a date, 7 weeks in the past.  I have a table that is essentially logging
some information and want to write a trigger that is data driven in trimming
old values out of the log, that way if I thought that 4 weeks was sufficient,
I could adjust the value.

Are you absolutely sure you want to do that? This would prune old
log entries on every insert. Why not trimming via crontab?