How do I use GMT times in postgresql?

Question

We are running about in our java code with GregorianCalendar. We would like to persist these in our postgresql database as GMT +0 time in timestamp type columns, but we're not sure how to go about it. Should the fields be with time stamp or without? By a similar token which "current" time postgres function should we be using current_timestamp or localtimestamp?

Answer 1

Start here - section 8.5.3. "Time Zones" from the documentation.

The difference between CURRENT_TIMESTAMP and LOCALTIMESTAMP is the type of the result - the former returns "timestamp with time zone" and the latter - "timestamp":

milen=> select CURRENT_TIMESTAMP;
              now
-------------------------------
 2009-09-05 01:21:37.595704+03
(1 row)

milen=> select LOCALTIMESTAMP;
         timestamp
----------------------------
 2009-09-05 01:21:41.956355
(1 row)

It's another matter entirely at which time zone you want to see the current time. In this case the default time zone (that could set in several ways, all described in the section linked above) is important. Also you could use "AT TIME ZONE" in a query to get the current time at specific time zone without "fiddling" with the "timezone" setting:

milen=> select CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
          timezone
----------------------------
 2009-09-04 22:21:44.418236
(1 row)