Thread: subtracting minutes from date

What is the best way to store a timestamp if all I need to do is
select rows where this timestamp is less than 60 minutes prior to the
current time?

If I have a column called date with data type timestamp without time
zone I know I can use

  SELECT * FROM table WHERE date < (now()::DATE - 7)::TIMESTAMP;

to select rows where date is older than seven days.  I'm not quite
sure how to interpret now()::DATE::TIMESTAMP since I'm not able to
answer my own question.  I've tried things like:

  SELECT * FROM table WHERE date < now()::DATE::(TIMESTAMP - 60);

but this gives a syntax error.

Thanks.

--
Brandon

You can just save it as timestamp and try the following query.

select * from table where date < (now() - interval '1 hour');

Regards
Chandra Sekhar Surapaneni

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Brandon Metcalf
Sent: Thursday, February 23, 2006 1:56 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] subtracting minutes from date

What is the best way to store a timestamp if all I need to do is select
rows where this timestamp is less than 60 minutes prior to the current
time?

If I have a column called date with data type timestamp without time
zone I know I can use

  SELECT * FROM table WHERE date < (now()::DATE - 7)::TIMESTAMP;

to select rows where date is older than seven days.  I'm not quite sure
how to interpret now()::DATE::TIMESTAMP since I'm not able to answer my
own question.  I've tried things like:

  SELECT * FROM table WHERE date < now()::DATE::(TIMESTAMP - 60);

but this gives a syntax error.

Thanks.

--
Brandon

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

On Thu, 2006-02-23 at 13:55, Brandon Metcalf wrote:
> What is the best way to store a timestamp if all I need to do is
> select rows where this timestamp is less than 60 minutes prior to the
> current time?
>
> If I have a column called date with data type timestamp without time
> zone I know I can use
>
>   SELECT * FROM table WHERE date < (now()::DATE - 7)::TIMESTAMP;
>
> to select rows where date is older than seven days.  I'm not quite
> sure how to interpret now()::DATE::TIMESTAMP since I'm not able to
> answer my own question.  I've tried things like:
>
>   SELECT * FROM table WHERE date < now()::DATE::(TIMESTAMP - 60);

Tell it you're subracting a minute:

select now() - interval '13 minutes';

That kind of thing

c == chandu@positivenetworks.net writes:

 c> You can just save it as timestamp and try the following query.

 c> select * from table where date < (now() - interval '1 hour');

Thanks.

--
Brandon

s == smarlowe@g2switchworks.com writes:

 s> On Thu, 2006-02-23 at 13:55, Brandon Metcalf wrote:
 s> > What is the best way to store a timestamp if all I need to do is
 s> > select rows where this timestamp is less than 60 minutes prior to the
 s> > current time?
 s> >
 s> > If I have a column called date with data type timestamp without time
 s> > zone I know I can use
 s> >
 s> >   SELECT * FROM table WHERE date < (now()::DATE - 7)::TIMESTAMP;
 s> >
 s> > to select rows where date is older than seven days.  I'm not quite
 s> > sure how to interpret now()::DATE::TIMESTAMP since I'm not able to
 s> > answer my own question.  I've tried things like:
 s> >
 s> >   SELECT * FROM table WHERE date < now()::DATE::(TIMESTAMP - 60);

 s> Tell it you're subracting a minute:

 s> select now() - interval '13 minutes';

Thanks.

--
Brandon

On Thu, Feb 23, 2006 at 03:08:32PM -0600, Scott Marlowe wrote:
> >   SELECT * FROM table WHERE date < now()::DATE::(TIMESTAMP - 60);
>
> Tell it you're subracting a minute:
>
> select now() - interval '13 minutes';

Or, better yet if you're feeding in a variable:

date < now() - ( 13 * '1 minute'::interval )
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Thu, Feb 23, 2006 at 13:55:34 -0600,
  Brandon Metcalf <bmetcalf@nortel.com> wrote:
>
> If I have a column called date with data type timestamp without time
> zone I know I can use
>
>   SELECT * FROM table WHERE date < (now()::DATE - 7)::TIMESTAMP;

You can do this without converting to timestamp:
SELECT * FROM table WHERE date < current_date - 7;