Обсуждение: timestamp

We currently have several tables that have columns defined as timestamp.

Does simply defining the column as timestamp imply "with time zone"?

This timestamp field contains data such as:
 2002-05-28 07:19:41-04

However, when I pg_dump this table and then pg_restore it on another database, this
timestamp field now becomes:
 2002-05-28 04:19:41-07

How do I maintain the same value for the timestamp fields when I restore
tables that have timestamp columns?

Thanks,

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Where's my....bus?

Ok, I figured it out...using the PGTZ environment variable.

Sorry for the un-necessary post.

L.
On Thu, 30 May 2002, Laurette Cisneros wrote:

>
> We currently have several tables that have columns defined as timestamp.
>
> Does simply defining the column as timestamp imply "with time zone"?
>
> This timestamp field contains data such as:
>  2002-05-28 07:19:41-04
>
> However, when I pg_dump this table and then pg_restore it on another database, this
> timestamp field now becomes:
>  2002-05-28 04:19:41-07
>
> How do I maintain the same value for the timestamp fields when I restore
> tables that have timestamp columns?
>
> Thanks,
>
>

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Where's my....bus?

> We currently have several tables that have columns defined as timestamp.
> Does simply defining the column as timestamp imply "with time zone"?

Yes. But the next release will have the default be "without time zone"
to comply with SQL9x specs.

> This timestamp field contains data such as:
>  2002-05-28 07:19:41-04
> However, when I pg_dump this table and then pg_restore it on another database, this
> timestamp field now becomes:
>  2002-05-28 04:19:41-07
> How do I maintain the same value for the timestamp fields when I restore
> tables that have timestamp columns?

These are the same values. It is the same absolute time represented in
different time zones. Your solution of using PGTZ is the right one if
you want to ensure that all times are displayed in the same time zone.

                     - Thomas