Обсуждение: problem insert time into column timestamp
When working with Ingres, it is possible to create a field "date" and insert into this a time value e.g. '10:12:12' the current date is assigned automatically by Ingres e.g. '31/07/2002 10:12:12' When working with Postgres, I receive an error when trying to insert a time value into a timestamp field gedis30=# create table pippo (aa timestamp)\g gedis30=# insert into pippo (aa) values('10:12:12')\g ERROR: Bad timestamp external representation '10:12:12' How can I solve this problem ? Bye !! Frank Lupo (Wolf) !! -- Prendi GRATIS l'email universale che... risparmia: http://www.email.it/f Sponsor: Il nostro catalogo completo a casa tua, gratis! Vieni da Peraga, tanti prodotti introvabili per te. Clicca qui: http://adv2.email.it/cgi-bin/foclick.cgi?mid=450&d=31-7
do not use the time stamp field use time if you are doing a time instead of a timestamp and for the date use a pure date field HTH On Wed, 31 Jul 2002, frank_lupo wrote: > When working with Ingres, it is possible to create a field "date" > and insert into this a time value e.g. '10:12:12' > the current date is assigned automatically by Ingres > e.g. '31/07/2002 10:12:12' > > When working with Postgres, I receive an error when trying to insert a > time value into a timestamp field > gedis30=# create table pippo (aa timestamp)\g > > gedis30=# insert into pippo (aa) values('10:12:12')\g > ERROR: Bad timestamp external representation '10:12:12' > > How can I solve this problem ? > > Bye !! > Frank Lupo (Wolf) !! > > > > -- > Prendi GRATIS l'email universale che... risparmia: http://www.email.it/f > > Sponsor: > Il nostro catalogo completo a casa tua, gratis! Vieni da Peraga, tanti prodotti introvabili per te. > Clicca qui: http://adv2.email.it/cgi-bin/foclick.cgi?mid=450&d=31-7 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Darren Ferguson
frank_lupo a écrit: > When working with Ingres, it is possible to create a field "date" > and insert into this a time value e.g. '10:12:12' > the current date is assigned automatically by Ingres > e.g. '31/07/2002 10:12:12' > > When working with Postgres, I receive an error when trying to insert a > time value into a timestamp field > gedis30=# create table pippo (aa timestamp)\g > > gedis30=# insert into pippo (aa) values('10:12:12')\g > ERROR: Bad timestamp external representation '10:12:12' > > How can I solve this problem ? gedis30=# insert into pippo (aa) values(cast ('10:12:12' as time));
> When working with Ingres, it is possible to create a field "date" > and insert into this a time value e.g. '10:12:12' > the current date is assigned automatically by Ingres > e.g. '31/07/2002 10:12:12' Hmm. That is a big jump to assume that a date/time value missing *all* date information should default those fields to "today". Not supported in standards at all afaik. What is the behavior if you try entering in *no* information at all? Do you get today's date at midnight? Does anyone know if other databases have the same behavior? > When working with Postgres, I receive an error when trying to insert a > time value into a timestamp field > gedis30=# insert into pippo (aa) values('10:12:12')\g > ERROR: Bad timestamp external representation '10:12:12' Right. You can give it a hint: lockhart=> select timestamp 'today 10:12:12'; timestamptz ------------------------ 2002-08-01 10:12:12-07 But what is your use case? Do you have users entering in dates and you want them to be able to skip the date fields if they know it is for today? If so, you might think about having your application preload the date fields with correct values?? - Thomas