On Thu, Mar 18, 2021 at 3:48 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > Robert Haas <robertmh...@gmail.com> writes: > > It's not very obvious how to scale this kind of approach to a wide > > variety of calendar types, and as Thomas says, it would much cooler to > > be able to handle all of the ones that ICU knows how to support rather > > than just one. But, the problem I see with using timestamptz is that > > it's not so obvious how to get a different output format ... unless, I > > guess, we can cram it into DateStyle. And it's also much less obvious > > how you get the other functions and operators to do what you want, if > > it's different. > > Yeah, I'm afraid that it probably is different. The most obvious > example is in operations involving type interval: > select now() + '1 month'::interval; > That should almost certainly give a different answer when using a > different calendar --- indeed the units of interest might not even > be the same. (Do all human calendars use the concept of months?)
Right, so if this is done by trying to extend Daniel Verite's icu_ext extension (link given earlier) and Robert's idea of a fast-castable type, I suppose you might want now()::icu_date + '1 month'::internal to advance you by one Ethiopic month if you have done SET icu_ext.ICU_LC_TIME = 'am_ET@calendar=traditional'. Or if using my first idea of just sticking with the core types, perhaps you'd have to replace stuff via search path... I admit that sounds rather error prone and fragile (I was thinking mainly of different functions, not operators). Either way, I suppose there'd also be more explicit functions for various operations including ones that take an extra argument if you want to use an explicit locale instead of relying on the ICU_LC_TIME setting. I dunno. As for whether all calendars have months, it looks like ICU's model has just the familiar looking standardised fields; whether some of them make no sense in some calendars, I don't know, but it has stuff like x.get(field, &error), x.set(field, &error), x.add(field, amount, &error) and if it fails for some field on your particular calendar, or for some value (you can't set a Gregorian date's month to 13 (apparently we call this month "undecember", hah), but you can for a Hebrew or Ethiopic one) I suppose we'd just report the error? > I don't feel like DateStyle is chartered to affect the behavior > of datetime operators; it's understood as tweaking the I/O behavior > only. There might be more of a case for letting LC_TIME choose > this behavior, but I bet the relevant standards only contemplate About LC_TIME... I suppose in one possible future we eventually use ICU for more core stuff, and someone proposes to merge hypothetical icu_date etc types into the core date etc types, and then LC_TIME controls that. But then you might have a version of the problem that Peter E ran into in attempts so far to use ICU collations as the default: if you put ICU's funky extensible locale names into the LC_XXX environment variables, then your libc will see it too, and might get upset, since PostgreSQL uses the en. I suspect that ICU will understand typical libc locale names, but common libcs won't understand ICU's highly customisable syntax, but I haven't looked into it. If that's generally true, then perhaps the solution to both problems is a kind of partial separation: regular LC_XXX, and then also ICU_LC_XXX which defaults to the same value but can be changed to access more advanced stuff, and is used only for interacting with ICU. > Gregorian calendars. Also, the SQL spec says in so many words > that the SQL-defined datetime types follow the Gregorian calendar. :-(
