Обсуждение: datediff function

Hi everyone,

Does anyone know if postgres has a function similar to what datediff does in
mssql server? I need it to do an update similar to the one below:

"update schedule set purged = 0 where datediff(day, timein, getdate()) > 30"

I know i could pull the whole table down to my machine, modify the data and
then upload it back, but that's really stupid not to mention what it'll do
to network trafic.

Thank you very much for any answer,
Thinh

I think what you are looking for is age()
E.g.

"update schedule set purged = 0 where age('now',dayin) > timespan('30 days'::reltime)"

Presuming a table such as this:

create table schedule (purged int, dayin datetime);

This replaces "day" and "timein" with "dayin."



Troy

>
> Hi everyone,
>
> Does anyone know if postgres has a function similar to what datediff does in
> mssql server? I need it to do an update similar to the one below:
>
> "update schedule set purged = 0 where datediff(day, timein, getdate()) > 30"
>
> I know i could pull the whole table down to my machine, modify the data and
> then upload it back, but that's really stupid not to mention what it'll do
> to network trafic.
>
> Thank you very much for any answer,
> Thinh
>
>

At 02:26 +0300 on 17/08/1999, tjk@tksoft.com wrote:


>
> I think what you are looking for is age()
> E.g.
>
> "update schedule set purged = 0 where age('now',dayin) > timespan('30
>days'::reltime)"
>
> Presuming a table such as this:
>
> create table schedule (purged int, dayin datetime);
>
> This replaces "day" and "timein" with "dayin."

Basically correct, but if there is an index on dayin, it won't be used. The
best query to do would be

WHERE dayin > 'now'::datetime - '30 days'::timespan;

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma