Postgres Pro
Downloads
Home   >   mailing lists

Thread: select using date

select using date

From
Kevin Heflin
Date:
Trying to use select statement using CURRENT_DATE
Which works fine like so:

select * from headlines where dateof = CURRENT_DATE order by dateof desc


But I'm also wanting to do something similar to:


select * from headlines where dateof = CURRENT_DATE - INTERVAL '1' DAY
order by dateof desc

Basically just trying to subtract 1 day from the CURRENT_DATE

When I try the above I get

ERROR:  parser: parse error at or near "day"

Any suggestions would be appreciated.


Kevin



--------------------------------------------------------------------
Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------

Re: [GENERAL] select using date

From
Tim Williams
Date:
> Trying to use select statement using CURRENT_DATE
> Which works fine like so:
>
> select * from headlines where dateof = CURRENT_DATE order by dateof desc
>
> But I'm also wanting to do something similar to:
>
> select * from headlines where dateof = CURRENT_DATE - INTERVAL '1' DAY
> order by dateof desc
>
> Basically just trying to subtract 1 day from the CURRENT_DATE

Kevin,

    (1) Handy hint: try '\df' once in the psql utility.
    This gives a nice list of functions, their return types, and
    brief descriptions of what they accomplish.

    (2) The function for which you seek is probably "timemi" which
    takes a time as its first argument and a time interval
    (like 1 day) as its second argument.

    For example, same time yesterday:

        select timemi('now'::datetime, '1 day'::timespan);

- Tim

Views

From
Kaare Rasmussen
Date:
How can I see a view in psql after having created it? If I enter
\d <viewname> I can see the fields, but how can I see what makes up the
view? Best if the original CREATE statement could be listed somehow.

Re: [GENERAL] select using date

From
"Jose' Soares"
Date:
Try:

select  current_date, CURRENT_DATE - INTERVAL '1 DAY';
  ?column?|?column?
----------+----------------------
1999-01-08|1999-01-07 00:00:00+01
(1 row)

PostgreSQL has a syntax sligth different than SQL92. You have to enclose
'1 DAY' instead of '1' DAY.

-Jose'-

Kevin Heflin wrote:
>
> Trying to use select statement using CURRENT_DATE
> Which works fine like so:
>
> select * from headlines where dateof = CURRENT_DATE order by dateof desc
>
> But I'm also wanting to do something similar to:
>
> select * from headlines where dateof = CURRENT_DATE - INTERVAL '1' DAY
> order by dateof desc
>
> Basically just trying to subtract 1 day from the CURRENT_DATE
>
> When I try the above I get
>
> ERROR:  parser: parse error at or near "day"
>
> Any suggestions would be appreciated.
>
> Kevin
>
> --------------------------------------------------------------------
> Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
> VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
> kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
> --------------------------------------------------------------------

Re: [GENERAL] select using date

From
Kevin Heflin
Date:
On Fri, 8 Jan 1999, Jose' Soares wrote:

> Try:
>
> select  current_date, CURRENT_DATE - INTERVAL '1 DAY';
>   ?column?|?column?
> ----------+----------------------
> 1999-01-08|1999-01-07 00:00:00+01
> (1 row)
>
> PostgreSQL has a syntax sligth different than SQL92. You have to enclose
> '1 DAY' instead of '1' DAY.


Well, I tried:

select  current_date, CURRENT_DATE - INTERVAL '1 DAY';

but receive the following:

ERROR:  There is no operator '-' for types 'date' and 'timespan'
        You will either have to retype this query using an explicit cast,
        or you will have to define the operator using CREATE OPERATOR


After trying somethings, I was able to get this to work:

select CURRENT_DATE -1;

will subtract 1 day from the current date... which gives me what I need
for now.


Thanks

Kevin







--------------------------------------------------------------------
Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
VP/Mac Tech           | 333 Texas St #619    | FAX:318.221.6612
kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------