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.