Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!gegeweb.org!de-l.enfer-du-nord.net!feeder1.enfer-du-nord.net!weretis.net!feeder1.news.weretis.net!news.solani.org!.POSTED!not-for-mail From: Mladen Gogala Newsgroups: comp.databases.postgresql Subject: Re: Timestamp with Timezone (Oracle versus Postgres) Date: Fri, 10 Jun 2011 13:39:03 +0000 (UTC) Organization: solani.org Lines: 59 Message-ID: References: <77db128d-ae4c-4ab9-ad2d-62b2e885d48b@r27g2000prr.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Trace: solani.org 1307713143 30475 eJwNyckBwDAIA7CVTIihHYdz/xFafUU1sfJrtMvlGlSfBgSowoq0MTc0C4z3TNffIxo8ueP+ARFrET4= (10 Jun 2011 13:39:03 GMT) X-Complaints-To: abuse@news.solani.org NNTP-Posting-Date: Fri, 10 Jun 2011 13:39:03 +0000 (UTC) User-Agent: Pan/0.133 (House of Butterflies) X-User-ID: eJwFwYEBwDAEBMCVCK+MIz72H6F3sNCYzwPhWGy/MRVO3lJB7kis4V2WVyXthOCQbc2+vf4DJjwRuA== Cancel-Lock: sha1:6NaeK6EyUIuy3uwUR5WuOsFPtM0= X-NNTP-Posting-Host: eJwFwQkBwDAIA0BLgRI65JTPv4Td8bh4XXO6cbn7GfNMhq3iLrxaJnXHmtQ3IQlRdEPfl7eeLdqoH7JwxWY1eNQbEhXyfuJRGdM= Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:129 On Fri, 10 Jun 2011 03:30:48 -0700, vinu wrote: > 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 ? 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. -- http://mgogala.byethost5.com