Postgres Pro
Downloads
Home   >   mailing lists

Thread: time type strange behaviour

time type strange behaviour

From
garry saddington
Date:
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

Re: time type strange behaviour

From
Tom Lane
Date:
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

Re: time type strange behaviour

From
garry saddington
Date:
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

Re: time type strange behaviour

From
Michael Fuhr
Date:
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