Обсуждение: time type strange behaviour

I have the following table def.


CREATE TABLE periods
(
  periodid serial NOT NULL,
  periodnumber integer NOT NULL,
  periodstart time with time zone,
  periodend time with time zone,
  PRIMARY KEY (periodid)
)

This definition does not insert time zone. If I use without time zone
then the time zone is inserted.
If I input a time like: 01:05 AM then on select I get something like:
1970/01/01 01:05:00:00.
If I use without time zone, then I get the correct date,time and time
zone.
The postgres version is 8.1 and I am using psycopg through Zope.

I have date-style set to 'iso dmy'
What I want is to just have the time inserted. Any ideas?
regards
garry

garry saddington <garry@schoolteachers.co.uk> writes:
> I have the following table def.
> CREATE TABLE periods
> (
>   periodid serial NOT NULL,
>   periodnumber integer NOT NULL,
>   periodstart time with time zone,
>   periodend time with time zone,
>   PRIMARY KEY (periodid)
> )

> This definition does not insert time zone. If I use without time zone
> then the time zone is inserted.
> If I input a time like: 01:05 AM then on select I get something like:
> 1970/01/01 01:05:00:00.

Better look again --- if you get that output, the column is most
certainly not a time column --- it must be timestamp.  Perhaps you
got confused about which table is which?

            regards, tom lane

On Tue, 2006-10-10 at 11:58 -0400, Tom Lane wrote:
> garry saddington <garry@schoolteachers.co.uk> writes:
> > I have the following table def.
> > CREATE TABLE periods
> > (
> >   periodid serial NOT NULL,
> >   periodnumber integer NOT NULL,
> >   periodstart time with time zone,
> >   periodend time with time zone,
> >   PRIMARY KEY (periodid)
> > )
>
> > This definition does not insert time zone. If I use without time zone
> > then the time zone is inserted.
> > If I input a time like: 01:05 AM then on select I get something like:
> > 1970/01/01 01:05:00:00.
>
> Better look again --- if you get that output, the column is most
> certainly not a time column --- it must be timestamp.  Perhaps you
> got confused about which table is which?
>
>             regards, tom lane
No, there is no confusion, I have dropped it, re-made it and tested it
again, same result. I know it sounds odd but this is what happens!
Garry

On Tue, Oct 10, 2006 at 05:16:14PM +0100, garry saddington wrote:
> On Tue, 2006-10-10 at 11:58 -0400, Tom Lane wrote:
> > garry saddington <garry@schoolteachers.co.uk> writes:
> > > This definition does not insert time zone. If I use without time zone
> > > then the time zone is inserted.
> > > If I input a time like: 01:05 AM then on select I get something like:
> > > 1970/01/01 01:05:00:00.
> >
> > Better look again --- if you get that output, the column is most
> > certainly not a time column --- it must be timestamp.  Perhaps you
> > got confused about which table is which?
> >
> No, there is no confusion, I have dropped it, re-made it and tested it
> again, same result. I know it sounds odd but this is what happens!

You said you were using psycopg and Zope, which implies that you're
using Python.  What versions of those things are you using?  Might
the unexpected results be coming from one of those components?  If
you connect to the database with psql and issue a query from there,
what do you get?

--
Michael Fuhr