Thread

1. Re: [SQL] sql date functions

   BD Postgres <postgres@master.abrente.es> — 1998-07-06T11:09:22Z

   Aleksey Dashevsky wrote:
   
   > I think you need to rewrite this query with something like:
   >
   > insert into table1 (date1,date2) values('1997-2-3'::date,'1997-2-3'::date
   >  + '1 week'::timespan);
   >
   > You can also use standard SQL-92 syntax for type casting, e.x.:
   > cast ('1 week' as timespan )
   >  etc.
   
   
   You are right, but now postgres is reporting me that there is no '+' operator
   defined for time and timespan, but I have found that I can add integers to
   dates and they are threaded as days so I can solve my problem easily.
   
   Thank you very much!
   
     - Salva.
   
   
   

2. Re: [SQL] sql date functions

   Aleksey Dashevsky <postgres@luckynet.co.il> — 1998-07-06T11:26:22Z

   
   On Mon, 6 Jul 1998, BD Postgres wrote:
   
   > Aleksey Dashevsky wrote:
   > 
   > > I think you need to rewrite this query with something like:
   > >
   > > insert into table1 (date1,date2) values('1997-2-3'::date,'1997-2-3'::date
   > >  + '1 week'::timespan);
   > >
   > > You can also use standard SQL-92 syntax for type casting, e.x.:
   > > cast ('1 week' as timespan )
   > >  etc.
   > 
   > 
   > You are right, but now postgres is reporting me that there is no '+' operator
   > defined for time and timespan, but I have found that I can add integers to
   > dates and they are threaded as days so I can solve my problem easily.
   > 
   
   Sorry, I really forgot: if you want to use date interval you need datetime
   type instead of date. Unforunately, postgres can only add timespan to
   datetime, but not to date, and  also it doesn't have autoconversion from
   date to datetime:-(
   
   So, you eihter need to change all mentions of date type with datetime
   (don't forget to change it also in "create table"), or you need to cast
   back datetime to date:
    
   insert into table1 (date1,date2) values('1997-2-3'::date,('1997-2-3'::datetime
     + '1 week'::timespan)::date );
   
   
   
   

3. Re: [SQL] sql date functions

   BD Postgres <postgres@master.abrente.es> — 1998-07-06T14:28:41Z

   Aleksey Dashevsky wrote:
   
   > Sorry, I really forgot: if you want to use date interval you need datetime
   > type instead of date. Unforunately, postgres can only add timespan to
   > datetime, but not to date, and  also it doesn't have autoconversion from
   > date to datetime:-(
   >
   > So, you eihter need to change all mentions of date type with datetime
   > (don't forget to change it also in "create table"), or you need to cast
   > back datetime to date:
   >
   > insert into table1 (date1,date2) values('1997-2-3'::date,('1997-2-3'::datetime
   >   + '1 week'::timespan)::date );
   
   I'm using now dates and integers and it seems to work from psql although it's
   giving me some problems from perl.
   
   insert into quinielas (id,temporada,jornada,inicio,final) values
   (13,1,1,'1-2-3'::date,'1-2-3'::date + 3);
   works from psql ok, but when I try to do it from perl with DBI with
   
   $sth->prepare('... values (?,?,?,?::date+?,?::date+?)');
   $sth->execute(13,1,1,'1-2-3',3,'1-2-3',5);
   
   it fails with a 'parameter unknown' error to the perl side although the postmaster
   do not report any error. It seems to me this is more from perl that from postgres,
   but anyway, some idea ?
   
     - Salva.