Thread: Date manipulation

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.

I've had a quick look around the docs and they're fairly spartan on details like this.

Can anybody point me to an online reference for pgsql overall and something that talks about the pitfalls and gives code examples of this kind of thing?

Regards.
    Hadley

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:

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)

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);

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

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:

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

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?