New date/time integration for cl-postgres

[Chaitanya Gupta]

On Tue, 19 Feb 2019 at 01:21, Attila Lendvai <attila at lendvai.name> wrote:
> i've spent a few minutes looking at the code and the TODO, and i
> couldn't identify any serious issues. all i remember is that, at some
> point a few years ago, we had to maintain a fork of local-time with
> our codebase that uses postgres and heavily relies on timestamps.
>
> maybe what i said is obsolete by now, because the major issue was that
> at some point a l-t timestamp structure (wrongly) included a timezone.
> a timezone is only a parameter of the printing and parsing functions
> of timestamps, that turn timestamps into a humanly understandable
> presentation (in the context of a calendar system and a timezone). i
> fixed that years ago, which was quite an overhaul of local-time.

Yes I noticed that. It was a very welcome change, and I am thankful you made it!

>
> some issues remained for which we had to sustain a fork, but i think
> they were not fundamental as the above mentioned confusion.
>
>
> > Also, are there any issues with parsing a timestamptz into a
> > LOCAL-TIME:TIMESTAMP?
>
>
> there are some nuisances due to the wrong understaing of timestamptz
> in postgres (timezones are irrelevant until the point you want to turn
> timestamps into a humanly readable form, or parse them from a humanly
> readable form).
>
> it's all fine when you parse a postgres timestamptz into a l-t
> timestamp. l-t will properly construct that point of time into a l-t
> timestamp, but the timezone part will be lost/discarded, so you cannot
> really produce a timestamptz from a l-t timestamp, unless you
> separately keep track of the timezone (and hope that postgres is using
> the same timezone database, because the politicians keep changing the
> summer time laws and stuff).
>
> maybe %split-timestring in l-t could be patched to not only return
> (offset-hour offset-minute), but something more complex to better
> denote a timezone.
>
> but basically timestamptz is a fauly idea, it shouldn't exist.
> everything should explicitly deal with a separate timestamp and a
> timezone designator -- *if* a timezone designator is needed, which is
> rarely a case.
>
> as a user, i want to see a timestampt presented to me in my preferred
> timezone, or in the timezone derived from the context the timestamp is
> presented in (i.e. the time of a location based meeting should be
> printed in the timezone relevant to the location; and for an
> international conference call, in everyone's local timezone).

I believe timestamptz behaves exactly as you say it should. Maybe it
behaved differently in earlier versions of Postgres (I never used
them), but as of now timestamptz doesn't carry any timezone info.

>From the docs:

> 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 (see Section 9.9.3).

https://www.postgresql.org/docs/current/datatype-datetime.html

Given this, I feel that local-time:timestamp is a perfect fit for timestamptz.

Chaitanya

>
> hth,
>
> --
> • attila lendvai
> • PGP: 963F 5D5F 45C7 DFCD 0A39
> --
> “All authority of any kind, especially in the field of thought and
> understanding, is the most destructive, evil thing. Leaders destroy
> the followers and followers destroy the leaders. You have to be your
> own teacher and your own disciple. You have to question everything
> that man has accepted as valuable, as necessary.”
> — Jiddu Krishnamurti (1895–1986), 'Freedom from the Known'



-- 
lisper.in
deftask.com