Convert UTC time field from POSTGRE / SQL database to SQL

Question

I have a problem with my database. I was never used to working in PostgreSQL, and I would like to make a choice from the datetime UTC field and get GMT time as the result.

Actually my request: select a date from the position What should be the Postgresql request to do what I want to do?

Thanks a lot Gwenael

Answer 1

PostgreSQL has two types of datetime :

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

I assume you have a table with a UTC date (without the 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)

datetime , 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

CHECK(), , UTC . , .