Postgresql: assign default timezone during table creation

Question

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

@null.pointer as I said in my question very clearly, `SET TIMEZONE TO 'GMT';` is not a solution for me. — Chris, Jun 03 '13 at 17:32
Read the documentation on Timezones in the postgresql documentation again. Timezones are based on the clients, not the server. SET TIMEZONE is most definitely a solution. — , Jun 03 '13 at 18:00
Also, you can check out the PGTZ environment variable to automate it. — , Jun 03 '13 at 18:11
Why not pass `+00` as the TZ offset instead of `+03` in your 1st insert or empty in the 2nd insert? That's how UTC is expressed in a `timestamptz` literal. In your example, it's not clear which exact part of the results you didn't expect or what you'd like instead. — Daniel Vérité, Jun 03 '13 at 20:33

score 1 · Answer 1 · edited May 23 '17 at 12:18

1

Time zone is a session parameter. It's relatively straight forward to set the session's TIMEZONE. Also see: postgres default timezone

SET TIMEZONE TO 'GMT'; will do the trick.

EDIT: I guess you could try AT TIMEZONE 'GMT';, but I'm not to sure on the difference between the two.

EDIT 2: As Craig Ringer said, the following seems to be what you're looking for.

ALTER DATABASE ... set it's database level. If you change it in postgresql.conf it's global. See the manual for details.

edited May 23 '17 at 12:18

Community

1
1

answered Jun 03 '13 at 17:32

@Chris - "SET TIMEZONE" is a solution for you, because it doesn't only work for a single session. You can apply it cluster-wide, to a specific database or a specific user. See the fine manuals for details. Oh - and this is nothing to do with the server's local timezone, but the clients. Take some time out to read the manual on date/time handling too. It's complicated, but then it has to be. – Richard Huxton Jun 03 '13 at 17:56
@RichardHuxton Thank you for the response. I have just tested it again on Postgresql 9.1 + Ubuntu 13.04. I can confirm again that it holds at session level only. Besides, I would like to have a solution during table creation – Chris Jun 03 '13 at 18:11
@Chris You're not really going to be able to handle this on the DB side unless you want to code your own logic into it. – Jun 03 '13 at 18:22
@null.pointer of course you can handle this in the db. chris - either you didn't apply the setting to the database or you are overriding it in the client. You also don't understand what a timestamptz really is. Please take the time to read the date/time and configuration settings of the manual - they explain it quite clearly. – Richard Huxton Jun 03 '13 at 19:24
2

@Chris If you use `SET` it's session-level. If you use `SET LOCAL` it's transaction level. If you use `ALTER USER ... SET` it's user level. If you use `ALTER DATABASE ... set` it's database level. If you change it in `postgresql.conf` it's global. See the manual for details. – Craig Ringer Jun 03 '13 at 23:49
@CraigRinger I think that's the correct answer. – Jun 04 '13 at 06:09

score 1 · Accepted Answer · answered Jun 03 '13 at 23:58

Dates, times and timestamps are pretty clumsy in SQL.

I suspect that in this case what you want to write will be:

TIMESTAMP 'blah blha blah' AT TIME ZONE 'UTC'

This will define a localtime timestamp (not subject to conversion) then explicitly treat it as a timestamptz in UTC time.

Postgresql: assign default timezone during table creation

2 Answers2