Is timestamp with timezone a ANSI SQL (or some other) standard data
type ? I noticing a difference in the way it is implemented in
Postgresql and Oracle. In Oracle it is possible to know the timezone
with which the data is inserted into the table (which is the usecase
of the data type i feel). See example (http://www.databasejournal.com/
features/oracle/article.php/3072991/Oracle-Time-Zone.htm)

In postgresql when you insert a record, the data is converted to UTC
and stored. When I select the record later on it shows the timestamp
converted to the session timezone. There is no way to know that
originally with what timezone the data was inserted in to the table.
In this an issue with postgresql or a conscious implementation
decision ? Is there an option to view the timezone with which the
timestamp is originally stored in the table at a later point of time
with having to add one additional column for that purpose ?

On Fri, 10 Jun 2011 03:30:48 -0700, vinu wrote:

Postgresql has different types for timestamp with or without time zone:
http://www.postgresql.org/docs/9.0/s...-datetime.html

When you describe the table, it will tell you which type was used:

scott=# \d emp
Table "public.emp"
Column | Type | Modifiers
----------+-----------------------------+-----------
empno | smallint | not null
ename | character varying(10) |
job | character varying(9) |
mgr | smallint |
hiredate | timestamp without time zone |
sal | double precision |
comm | double precision |
deptno | smallint |
Indexes:
"emp_pkey" PRIMARY KEY, btree (empno)
"emp_mgr_i" btree (mgr)
Foreign-key constraints:
"fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)

scott=#

Other than that, moving an existing Oracle application to PostgreSQL is
an ill advised adventure because PostgreSQL has no hints and has very
limited monitoring capabilities. It is not possible to force the
particular plan in PostgreSQL which will usually kill any porting
project. Hints are kept out of Postgres by the developers with hippie
mentality who haven't ever maintained a big database for living in their
careers. PostgreSQL is a sad story of what happens when the application
programmers who have no DBA experience take over a database. Of course,
they're still trying to figure out why is MySQL more popular than PgSQL.
In other words, if you are starting a project from scratch, it's OK, but
porting projects will usually fail, precisely because of lack of hints.



--
http://mgogala.byethost5.com

On 2011-06-10, vinu <vinu.rm (AT) gmail (DOT) com> wrote:
yes, there are rules about how it behaves and as I understand it
postgres mets all the requirements.

oracle displays the timezone offset, which is not the same thing as
the timezone,

as far as I can tell is was a conscious decision.

no. you need an extra column.

--
⚂⚃ 100% natural

Jasen Betts wrote:> On 2011-06-10, vinu <vinu.rm (AT) gmail (DOT) com> wrote:
Maybe PostgreSQL meets all the requirements, but as I researched here
http://archives.postgresql.org/pgsql...6/msg01372.php
the Standard seems to suggest that the time zone information should be
stored along with the date.
Tom Lane did not seem to disagree:
http://archives.postgresql.org/pgsql...6/msg01389.php

I didn't find anything in the Standard that is explicit on how time zone
information should be handled, but it seems that what the writers had in
mind was closer to Oracle's implementation than to PostgreSQL's.

[...]

Even if, Tom Lane's remark quoted above indicates that there have been
debates (I'm too lazy to research), and the status quo is retained at least
partly because other options would be inconvenient to implement.

Yours,
Laurenz Albe