Thread

1. Re: [GENERAL] select using date

   Tim Williams <williams@ugsolutions.com> — 1999-01-05T11:06:18Z

   > 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
   
   

2. select using date

   Kevin Heflin <kheflin@shreve.net> — 1999-01-05T16:55:53Z

   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
   --------------------------------------------------------------------
   
   
   

3. Views

   Kaare Rasmussen <kar@webline.dk> — 1999-01-05T19:04:10Z

   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.
   
   
   

4. Re: [GENERAL] Views

   Vadim Mikheev <vadim@krs.ru> — 1999-01-06T01:58:09Z

   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
   
   

5. Re: [HACKERS] Re: [GENERAL] Views

   Bruce Momjian <maillist@candle.pha.pa.us> — 1999-01-06T02:21:39Z

   > 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
   
   

6. Re: [HACKERS] Re: [GENERAL] Views

   Jan Wieck <jwieck@debis.com> — 1999-01-06T10:03:33Z

   > 
   > 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) #
   
   
   

7. Re: [GENERAL] select using date

   Jose Soares <jose@sferacarta.com> — 1999-01-08T12:37:57Z

   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
   > --------------------------------------------------------------------
   
   

8. Re: [GENERAL] select using date

   Kevin Heflin <kheflin@shreve.net> — 1999-01-08T14:52:38Z

   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
   --------------------------------------------------------------------