> 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

Tue, 05 Jan 1999 11:06:18 CST
Tim Williams <williams@ugsolutions.com>
Re: [GENERAL] select using 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
--------------------------------------------------------------------


Tue, 5 Jan 1999 10:55:53 -0600 (CST)
Kevin Heflin <kheflin@shreve.net>
select using 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.


Tue, 5 Jan 1999 20:04:10 +0100 (CET)
Kaare Rasmussen <kar@webline.dk>
Views
Kaare Rasmussen wrote:
> 
> 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.

We could use EXPLAIN VIEW for this...
New item for TODO?

EXPLAIN TABLE, EXPLAIN INDEX, etc

Vadim

Wed, 06 Jan 1999 08:58:09 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [GENERAL] Views
> Kaare Rasmussen wrote:
> > 
> > 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.
> 
> We could use EXPLAIN VIEW for this...
> New item for TODO?
> 
> EXPLAIN TABLE, EXPLAIN INDEX, etc

How about:

	SELECT * from pg_views
	WHERE viewname = 'fred';

OR

	SELECT pg_get_viewdef('fred');

I have added this to the TODO list:

	* have psql \d on a view show the view definition.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 5 Jan 1999 21:21:39 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re: [GENERAL] Views
> 
> Kaare Rasmussen wrote:
> > 
> > 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.
> 
> We could use EXPLAIN VIEW for this...
> New item for TODO?
> 
> EXPLAIN TABLE, EXPLAIN INDEX, etc
> 
> Vadim
> 
> 

    SELECT * FROM pg_views;


Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #


Wed, 6 Jan 1999 11:03:33 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] Re: [GENERAL] Views
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
> --------------------------------------------------------------------

Fri, 08 Jan 1999 13:37:57 +0100
"Jose' Soares" <jose@sferacarta.com>
Re: [GENERAL] select using 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
--------------------------------------------------------------------


Fri, 8 Jan 1999 08:52:38 -0600 (CST)
Kevin Heflin <kheflin@shreve.net>
Re: [GENERAL] select using date