Date manipulation

[Hadley Willan]

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

[Alvaro Herrera]

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)

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Hadley Willan]

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)

[Bruno Wolff III]

On Mon, Apr 19, 2004 at 11:18:23 +1200,
Hadley Willan <ha***********@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);

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Hadley Willan <ha***********@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Bruno Wolff III <br***@wolff.to> writes:

Hadley Willan <ha***********@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Hadley Willan]

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 <br***@wolff.to> writes:

Hadley Willan <ha***********@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