Hi,
I created a new date/time integration for cl-postgres.
https://github.com/chaitanyagupta/cl-postgres-datetime
There's nothing fancy -- it just uses local-time to parse timestamptz and simple-date for other types (which don't use a timezone).
Why? Because neither local-time nor simple-date alone work well with all the date/time types that Postgres supports.
local-time's TIMESTAMP is a natural fit for timestamptz, and while non-timezone types like timestamp and date could be parsed into it, the user has to remember to always use +UTC-ZONE+ when decoding such a timestamp (the values of decoded components may mismatch with the original if the default time zone were different from UTC).
simple-date has no concept of time zones, so while it works well for every type that doesn't need a timezone, it fails badly when it comes to timestamptz.
Hope someone finds it useful.
Chaitanya --- lisper.in deftask.com
Why? Because neither local-time nor simple-date alone work well with all the date/time types that Postgres supports.
local-time was confused and it has some pending and serious TODO entries regarding timezone handling.
this needs to be cleaned up in local-time before a better postgres integration can be written.
it's doubtful i'll work on this any time soon, but i'm happy to answer questions and/or extend the l-t TODO file.
On Mon, 18 Feb 2019 at 21:47, Attila Lendvai attila@lendvai.name wrote:
local-time was confused and it has some pending and serious TODO entries regarding timezone handling.
this needs to be cleaned up in local-time before a better postgres integration can be written.
Can you explain what those issues are in the context of a postgres integration? I went through the l-t TODO file but I am unable to figure out which ones you are concerned about.
Also, are there any issues with parsing a timestamptz into a LOCAL-TIME:TIMESTAMP?
Chaitanya
it's doubtful i'll work on this any time soon, but i'm happy to answer questions and/or extend the l-t TODO file.
-- • attila lendvai • PGP: 963F 5D5F 45C7 DFCD 0A39 -- “War must be, while we defend our lives against a destroyer who would devour all; but I do not love the bright sword for its sharpness, nor the arrow for its swiftness, nor the warrior for his glory. I love only that which they defend.” — J. R. R. Tolkien (1892–1973), 'The Two Towers' (1954), http://youtu.be/jfxdlWje5nk
TLDR: a timezone is mostly a property of the presentation, and rarely should be saved as a part of the data. l-t currently doesn't support a timestamptz class, and probably it shouln't either. maybe the postgres - local-time integration could introduce a new timestamptz class, but l-t shouldn't know anything about it.
this needs to be cleaned up in local-time before a better postgres integration can be written.
Can you explain what those issues are in the context of a postgres integration? I went through the l-t TODO file but I am unable to figure out which ones you are concerned about.
hrm... i may be mis-remembering stuff, it's been a while. sorry if i wasted your time!
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.
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).
hth,
On Tue, 19 Feb 2019 at 01:21, Attila Lendvai attila@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'
https://www.postgresql.org/docs/current/datatype-datetime.html
Given this, I feel that local-time:timestamp is a perfect fit for timestamptz.
oh, hrm. maybe i just shouldn't try to play smart from a decade old memory... :)
sorry for the noise,
postmodern-devel@common-lisp.net
-
Attila Lendvai -
Chaitanya Gupta