Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #129

Re: Timestamp with Timezone (Oracle versus Postgres)

Show all headers | View raw

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

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Timestamp with Timezone (Oracle versus Postgres) vinu <vinu.rm@gmail.com> - 2011-06-10 03:30 -0700
  Re: Timestamp with Timezone (Oracle versus Postgres) Mladen Gogala <gogala.mladen@gmail.com> - 2011-06-10 13:39 +0000
  Re: Timestamp with Timezone (Oracle versus Postgres) Jasen Betts <jasen@xnet.co.nz> - 2011-06-12 10:32 +0000
    Re: Timestamp with Timezone (Oracle versus Postgres) "Laurenz Albe" <invite@spam.to.invalid> - 2011-06-14 11:43 +0200

csiph-web