Timestamp with Timezone (Oracle versus Postgres)
vinu
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 ?
Mladen Gogala
Postgresql has different types for timestamp with or without time zone:
http://www.postgresql.org/docs/9.0/static/datatype-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.
Jasen Betts
> Is timestamp with timezone a ANSI SQL (or some other) standard data
> type ?
yes, there are rules about how it behaves and as I understand it
postgres mets all the requirements.
> 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)
oracle displays the timezone offset, which is not the same thing as
the timezone,
> 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?
as far as I can tell is was a conscious 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 ?
no. you need an extra column.
--
⚂⚃ 100% natural
Laurenz Albe
>> Is timestamp with timezone a ANSI SQL (or some other) standard data
>> type ?
> yes, there are rules about how it behaves and as I understand it
> postgres mets all the requirements.
Maybe PostgreSQL meets all the requirements, but as I researched here
http://archives.postgresql.org/pgsql-general/2009-06/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-general/2009-06/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.
[...]
>> In this an issue with postgresql or a conscious implementation
>> decision?
> as far as I can tell is was a conscious decision.
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