Postgres Pro
Downloads
Home   >   mailing lists

Thread: time without time zone

time without time zone

From
Garry Saddington
Date:
This is a select on table periods defined as such:
CREATE TABLE periods
(
  periodid serial NOT NULL,
  periodnumber integer NOT NULL,
  periodstart time without time zone,
  periodend time without time zone,
  PRIMARY KEY (periodid)
)

Periodid      Periodnumber      Periodstart      Periodend
6     1     2007/06/18 09:00:00 GMT+0     2007/06/18 09:30:00 GMT+0
7     2     2007/06/18 09:30:00 GMT+0     2007/06/18 10:00:00 GMT+0
8     3     2007/06/18 10:00:00 GMT+0     2007/06/18 10:30:00 GMT+0
9     4     2007/06/18 10:30:00 GMT+0     2007/06/18 11:00:00 GMT+0
10     5     2007/06/18 11:30:00 GMT+0     2007/06/18 12:00:00 GMT+0
11     6     2007/06/18 13:00:00 GMT+0     2007/06/18 13:30:00 GMT+0
12     7     2007/06/18 13:30:00 GMT+0     2007/06/18 14:00:00 GMT+0
13     8     2007/06/18 14:00:00 GMT+0     2007/06/18 14:30:00 GMT+0

Can anyone explain why time has todays date and time zone? I am confused, I
only want time, such as:
13:00:00
regards
garry

Re: time without time zone

From
Richard Huxton
Date:
Garry Saddington wrote:
> This is a select on table periods defined as such:
> CREATE TABLE periods
> (
>   periodid serial NOT NULL,
>   periodnumber integer NOT NULL,
>   periodstart time without time zone,
>   periodend time without time zone,
>   PRIMARY KEY (periodid)
> )
>
> Periodid      Periodnumber      Periodstart      Periodend
> 6     1     2007/06/18 09:00:00 GMT+0     2007/06/18 09:30:00 GMT+0
> 7     2     2007/06/18 09:30:00 GMT+0     2007/06/18 10:00:00 GMT+0

> Can anyone explain why time has todays date and time zone? I am confused, I
> only want time, such as:
> 13:00:00

You don't say what version you're running, but I can't reproduce this
here on 8.2 - are you sure that table definition is right?

CREATE TABLE timetest (t1 time, t2 time without time zone, t3 timestamp
without time zone);
INSERT INTO timetest values (now(),now(),now());
SELECT * FROM timetest;
        t1        |       t2        |             t3
-----------------+-----------------+----------------------------
  21:12:30.346289 | 21:12:30.346289 | 2007-06-18 21:12:30.346289
(1 row)

--
   Richard Huxton
   Archonet Ltd

Re: time without time zone

From
Tom Lane
Date:
Garry Saddington <garry@schoolteachers.co.uk> writes:
> Can anyone explain why time has todays date and time zone?

Works for me:

regression=# insert into periods values(1,1,'now','now');
INSERT 0 1
regression=# select * from periods;
 periodid | periodnumber |  periodstart   |   periodend
----------+--------------+----------------+----------------
        1 |            1 | 16:13:14.35962 | 16:13:14.35962
(1 row)

I speculate that you are trying to display the table in some client
software that doesn't know the time datatype and is forcibly converting
it to something it does know.

            regards, tom lane

Re: time without time zone

From
Garry Saddington
Date:
On Monday 18 June 2007 21:15, Tom Lane wrote:
> Garry Saddington <garry@schoolteachers.co.uk> writes:
> > Can anyone explain why time has todays date and time zone?
>
> Works for me:
>
> regression=# insert into periods values(1,1,'now','now');
> INSERT 0 1
> regression=# select * from periods;
>  periodid | periodnumber |  periodstart   |   periodend
> ----------+--------------+----------------+----------------
>         1 |            1 | 16:13:14.35962 | 16:13:14.35962
> (1 row)
>
> I speculate that you are trying to display the table in some client
> software that doesn't know the time datatype and is forcibly converting
> it to something it does know.
>
>             regards, tom lane
Yes, you are correct I am in Zope using ZpsycopgDA. Just tried on the command
line and the behaviour is correct. Time to ask elsewhere, thanks.
regards
garry