pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsql date functions
4+ messages / 2 participants
[nested] [flat]
* sql date functions
@ 1998-07-06 10:14 BD Postgres <postgres@master.abrente.es>
0 siblings, 0 replies; 4+ messages in thread
From: BD Postgres @ 1998-07-06 10:14 UTC (permalink / raw)
To: pgsql-sql
Hello,
I am new to postgress and SQL and I don't know how to use the date
functions:
I need to insert two dates in a row, the first been a week before the
second but I don't know how this can be achieved, I have tried:
insert into table1 (date1,date2) values('1997-2-3::date','1997-2-3::date
+ timespan(1 week)');
that is what I think that should be, and all the variations I have
imagined, but it hasn't worked, I'm doing this from perl with the DBI
interface, but I don't think that to precompute the second date first in
perl is a good idea, I prefer to let postgress do it if possible.
the errors reported by postmaster are like this:
ERROR: Bad date external representation 1/2/2003::date + timespan(1
week)
Could somebody tell me which is the correct way to get this working ?
Thank you.
- Salvador FandiƱo.
P.S. I have previusly posted this msg in the NOVICE mailing list, but
nobody seems to know how to do it there.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] sql date functions
@ 1998-07-06 11:09 BD Postgres <postgres@master.abrente.es>
0 siblings, 1 reply; 4+ messages in thread
From: BD Postgres @ 1998-07-06 11:09 UTC (permalink / raw)
To: Aleksey Dashevsky <postgres@luckynet.co.il>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] sql date functions
@ 1998-07-06 11:26 Aleksey Dashevsky <postgres@luckynet.co.il>
parent: BD Postgres <postgres@master.abrente.es>
0 siblings, 1 reply; 4+ messages in thread
From: Aleksey Dashevsky @ 1998-07-06 11:26 UTC (permalink / raw)
To: BD Postgres <postgres@master.abrente.es>; +Cc: pgsql-sql
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 );
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] sql date functions
@ 1998-07-06 14:28 BD Postgres <postgres@master.abrente.es>
parent: Aleksey Dashevsky <postgres@luckynet.co.il>
0 siblings, 0 replies; 4+ messages in thread
From: BD Postgres @ 1998-07-06 14:28 UTC (permalink / raw)
To: Aleksey Dashevsky <postgres@luckynet.co.il>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1998-07-06 14:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-07-06 10:14 sql date functions BD Postgres <postgres@master.abrente.es>
1998-07-06 11:09 Re: [SQL] sql date functions BD Postgres <postgres@master.abrente.es>
1998-07-06 11:26 ` Aleksey Dashevsky <postgres@luckynet.co.il>
1998-07-06 14:28 ` BD Postgres <postgres@master.abrente.es>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox