Converting UTC time field from a POSTGRE/SQL database in SQL

Question

I am having a problem from my database. I never been used to work in PostgreSQL, and i would like to make a select from a UTC datetime field and getting a GMT Datetime as result.

Actually my request is : select dateheure from position What should be the Postgresql request to do what i want to do ???

Thanks a lot Gwenael

Answer 1

PostgreSQL does have two datetime types:

• timestamp without time zone (default implicit timestamp)
• timestamp with time zone

I guess that you have table with UTC datetime (without time zone type):

CREATE TEMP TABLE datetimetest
(
    datetime timestamp
);

\d datetimetest
           Table "pg_temp_1.datetimetest"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 datetime | timestamp without time zone | 

INSERT INTO datetimetest SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';

SELECT datetime FROM datetimetest;
          datetime          
----------------------------
 2011-08-15 15:04:06.507166
(1 row)

To get datetime in some timezone you could use AT TIME ZONE construct:

SET TIME ZONE 'UTC';
SELECT datetime AT TIME ZONE 'GMT-5' FROM datetimetest;
           timezone            
-------------------------------
 2011-08-15 10:04:06.507166+00
(1 row)

Answer 2

In a different post I use a CHECK() constraint to make sure that you only store and receive UTC out of the database. Hopefully it's helpful.