5

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

Gwenael
  • 137
  • 1
  • 2
  • 7

2 Answers2

12

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)
Grzegorz Szpetkowski
  • 36,988
  • 6
  • 90
  • 137
  • Just tried that : SELECT dateheure AT TIME ZONE 'GMT' FROM position; – Gwenael Aug 15 '11 at 15:45
  • and get that : ERREUR: la fonction pg_catalog.timezone(unknown, text) n'existe pas LINE 1: SELECT dateheure AT TIME ZONE 'GMT' FROM position; ^ HINT: Aucune fonction ne correspond au nom donné et aux types d'arguments. Vous devez ajouter des conversions explicites de type. – Gwenael Aug 15 '11 at 15:45
  • 1
    @Gwenael: Your `dateheure` column has `text` type, so it's not recognized here. Probably all you need is explicit cast to timestamp, that is: `SELECT dateheure::timestamp AT TIME ZONE 'GMT' FROM position;`. However to store date times probably you should use one of two timestamp types. – Grzegorz Szpetkowski Aug 15 '11 at 16:48
1

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.

Community
  • 1
  • 1
Sean
  • 9,888
  • 4
  • 40
  • 43