I have the following definition of employees table
CREATE TABLE employees
(
id integer NOT NULL,
name text,
withouttz timestamp without time zone,
withtz timestamp with time zone,
CONSTRAINT primarykey_emp PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE employees
OWNER TO chris;
I have inserted two records in the following way:
INSERT INTO employees(
id, name, withouttz, withtz)
VALUES (1, 'test', '2011-01-01 00:00:00', '2011-01-01 12:00:00+03');
INSERT INTO employees(
id, name, withouttz, withtz)
VALUES (2, 'test', '2011-01-01 00:00:00', '2011-01-01 12:00:00');
I have written simple java class for select * from employees which outputs the following:
col1: 1
col2: test
col3: 2011-01-01 00:00:00
col4: 2011-01-01 07:00:00-8:00
col1: 2
col2: test
col3: 2011-01-01 00:00:00
col4: 2011-01-01 12:00:00-8:00
Question:
Is there a way to create a postgres table's timestamp with time zone so that it considers the timezone to be UTC instead of server's local timezone ?
Note: set timezone TO 'GMT'; is not a solution for me because it is works in only a specific session. Also, it would be great if the solution doesnot depend on Server's local timezone at all
Thanks in advance
SET TIMEZONE TO 'GMT';is not a solution for me.+00as the TZ offset instead of+03in your 1st insert or empty in the 2nd insert? That's how UTC is expressed in atimestamptzliteral. In your example, it's not clear which exact part of the results you didn't expect or what you'd like instead.