Обсуждение: 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