Julien,

Are you thinking that PG will allow you to have different timezones in your timestamp? If my understanding of PG is correct, it keeps everything in a single timezone - UTC. Then everything else is set using the offset. See, e.g. http://www.postgresql.org/docs/9.1/static/datatype-datetime.html

"For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct."

So, looking at a server that is set for PDT, for table test with fields name, text and updated_at

Default with no timezone or offset:
(query (:insert-into 'test :set 'name "george" 'text "insert here"  'updated-at
                     (local-time:encode-timestamp 0 0 0 12 01 01 2013)))

2013-01-01 12:00:00-08 (looking at the default timezone for the server, PG has set the timezone to UTC less 8 hours - UTC time would be 04:00:00)

Using offset to explicitly offset 1 hour from UTC (e.g. Paris)
(query (:insert-into 'test :set 'name "ringo" 'text "offset 1 hour" 'updated-at
                     (local-time:encode-timestamp 0 0 0 12 01 01 2013 :offset 3600)))

2013-01-01 03:00:00-08 (looking at the default timezone for the server, PG has kept the timezone as PDT - UTC less 8 hours - but set the time as 03:00:00, which is 1 hour ahead of UTC)

Using timezone to explicitly set it for UTC
(query (:insert-into 'test
                     :set 'name "paul" 'text "insert here using timezone utc"
                     'updated-at
                     (local-time:encode-timestamp 0 0 0 12 01 01 2013
                                                  :timezone local-time::+utc-zone+)))

2013-01-01 04:00:00-08 (looking at the default timezone for the server, PG has kept the timezone as PDT - UTC less 8 hours - but set the time as 04:00:00, which is the time in UTC relative to the PDT time at the server.

Does this help?

Sabra


On Wed, Jul 24, 2013 at 12:14 AM, Julien Danjou <julien@danjou.info> wrote:
On Wed, Jul 24 2013, Sabra Crolleton wrote:

> I use the local-time package with encode-timestamp to create the timestamp
> and just put that into the database.
>
> Function: local-time:encode-timestamp nsec sec minute hour day month year
> &key timezone offset into
>
> Returns a new timestamp instance corresponding to the specified time
> elements. The offset is the number of seconds offset from UTC of the
> locale. If offset is not specified, the offset will be guessed from the
> timezone. If a timestamp is passed as the into argument, its value will be
> set and that timestamp will be returned. Otherwise, a new timestamp is
> created.

The problem is that it returns a timestamp without any timezone
information, it just convert the time given into an UTC representation
based on the timezone arguments.
Or when INSERTed, if your Postgresql session runs with a default
timezone different than UTC (which is usually the case), PG will insert
it considering the timezine is in the locale timestamp, not UTC.

When your PG server is in GMT+2 and has such a default session, if:
a. you INSERT (encode-timestamp 0 0 0 12 01 01 2013) it will insert
   2013-01-01 12:00:00+02
   (which is correct because the CL timezone and the PG timezone matches)

b. you INSERT (encode-timestamp 0 0 0 12 01 01 2013 :timezone UTC) it will insert
   2013-01-01 10:00:00+02
   (which is wrong since the timestamp is in UTC)

And here I'm on case b.

--
Julien Danjou
# Free Software hacker # freelance consultant
# http://julien.danjou.info