Thread: determining supported timezones
I am trying to figure out if there is a way to determine the timezones supported in postgresql from within the database. If you look at http://www.postgresql.org/docs/7.4/static/datetime-keywords.html it notes that time zone information is system dependent, (I interpret to mean that anything I find in /usr/share/zoneinfo on linux should be supported, can someone confirm that?) so how can an external app determine which timezones are supported given that it could be deployed against postgresql databases on different OS's. My current thinking is that there is no way to get a complete list, but perhaps the list of known timezones (as listed in the docs) are available? If it is not, will this change in 7.5 now that we have a standard timezone library we are using across platforms? Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>I am trying to figure out if there is a way to determine the timezones >supported in postgresql from within the database. If you look at >http://www.postgresql.org/docs/7.4/static/datetime-keywords.html it >notes that time zone information is system dependent, (I interpret to >mean that anything I find in /usr/share/zoneinfo on linux should be >supported, can someone confirm that?) Yes, that should be it. > so how can an external app >determine which timezones are supported given that it could be deployed >against postgresql databases on different OS's. My current thinking is >that there is no way to get a complete list, but perhaps the list of >known timezones (as listed in the docs) are available? If it is not, >will this change in 7.5 now that we have a standard timezone library we >are using across platforms? In 7.5, you can check the files in <pgdir>/share/timezone. There is no function in the backend ATM to show them. I've been thinking of adding one (as a system view), but didn't get around to it before freeze. (It'd basically loop over the files in the directory) //Magnus
TODO?
* Show supported times
---------------------------------------------------------------------------
Magnus Hagander wrote:
> >I am trying to figure out if there is a way to determine the timezones
> >supported in postgresql from within the database. If you look at
> >http://www.postgresql.org/docs/7.4/static/datetime-keywords.html it
> >notes that time zone information is system dependent, (I interpret to
> >mean that anything I find in /usr/share/zoneinfo on linux should be
> >supported, can someone confirm that?)
>
> Yes, that should be it.
>
>
> > so how can an external app
> >determine which timezones are supported given that it could be deployed
> >against postgresql databases on different OS's. My current thinking is
> >that there is no way to get a complete list, but perhaps the list of
> >known timezones (as listed in the docs) are available? If it is not,
> >will this change in 7.5 now that we have a standard timezone library we
> >are using across platforms?
>
> In 7.5, you can check the files in <pgdir>/share/timezone. There is no
> function in the backend ATM to show them. I've been thinking of adding
> one (as a system view), but didn't get around to it before freeze. (It'd
> basically loop over the files in the directory)
>
> //Magnus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Can I argue that this is a missing implementation detail needed for 7.5? My grounds being that we are potentially breaking backwards compatability by changing the supported timezones but giving the user no easy way to determine just what is supported. Robert Treat On Wednesday 14 July 2004 18:25, Bruce Momjian wrote: > TODO? > > * Show supported times > > --------------------------------------------------------------------------- > > Magnus Hagander wrote: > > >I am trying to figure out if there is a way to determine the timezones > > >supported in postgresql from within the database. If you look at > > >http://www.postgresql.org/docs/7.4/static/datetime-keywords.html it > > >notes that time zone information is system dependent, (I interpret to > > >mean that anything I find in /usr/share/zoneinfo on linux should be > > >supported, can someone confirm that?) > > > > Yes, that should be it. > > > > > so how can an external app > > >determine which timezones are supported given that it could be deployed > > >against postgresql databases on different OS's. My current thinking is > > >that there is no way to get a complete list, but perhaps the list of > > >known timezones (as listed in the docs) are available? If it is not, > > >will this change in 7.5 now that we have a standard timezone library we > > >are using across platforms? > > > > In 7.5, you can check the files in <pgdir>/share/timezone. There is no > > function in the backend ATM to show them. I've been thinking of adding > > one (as a system view), but didn't get around to it before freeze. (It'd > > basically loop over the files in the directory) > > > > //Magnus > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Yes, I think you could argue this. In the old system, we could tell them to look in the OS-supported timeszones. Now we have to tell them to look in data/share/timezone. Is that enough or not? --------------------------------------------------------------------------- Robert Treat wrote: > Can I argue that this is a missing implementation detail needed for 7.5? My > grounds being that we are potentially breaking backwards compatability by > changing the supported timezones but giving the user no easy way to determine > just what is supported. > > Robert Treat > > On Wednesday 14 July 2004 18:25, Bruce Momjian wrote: > > TODO? > > > > * Show supported times > > > > --------------------------------------------------------------------------- > > > > Magnus Hagander wrote: > > > >I am trying to figure out if there is a way to determine the timezones > > > >supported in postgresql from within the database. If you look at > > > >http://www.postgresql.org/docs/7.4/static/datetime-keywords.html it > > > >notes that time zone information is system dependent, (I interpret to > > > >mean that anything I find in /usr/share/zoneinfo on linux should be > > > >supported, can someone confirm that?) > > > > > > Yes, that should be it. > > > > > > > so how can an external app > > > >determine which timezones are supported given that it could be deployed > > > >against postgresql databases on different OS's. My current thinking is > > > >that there is no way to get a complete list, but perhaps the list of > > > >known timezones (as listed in the docs) are available? If it is not, > > > >will this change in 7.5 now that we have a standard timezone library we > > > >are using across platforms? > > > > > > In 7.5, you can check the files in <pgdir>/share/timezone. There is no > > > function in the backend ATM to show them. I've been thinking of adding > > > one (as a system view), but didn't get around to it before freeze. (It'd > > > basically loop over the files in the directory) > > > > > > //Magnus > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > > > http://archives.postgresql.org > > -- > Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Let's say that I want to efficiently compute something like a weighted
standard deviation (the actual formula I have in mind is slightly more
complicated). The kind of SQL statement I want to have work is
something like
SELECT weighted_stdev( t.val, t.weight ) FROM target_vals_tbl t
WHERE t.val > 0;
I thought I'd like to write a C function (or more properly a set of two
C functions) into the server side to handle this, and then declare it
as an aggregate using CREATE AGGREGATE. However, aggregate functions
appear to want just a single argument, so I feel like either I am on
the wrong track, or I have run into a limitation of postgresql.
What should I be doing here? Is there a way to do this with array
functions instead?
- Brian K. Boonstra
>> I am trying to figure out if there is a way to determine the timezones >> supported in postgresql from within the database. If you look at >> http://www.postgresql.org/docs/7.4/static/datetime-keywords.html it >> notes that time zone information is system dependent, That documentation is now out of date. > In 7.5, you can check the files in <pgdir>/share/timezone. There is no > function in the backend ATM to show them. I've been thinking of adding > one (as a system view), but didn't get around to it before freeze. The word "overengineering" comes to mind... We should simply list the available timezones in the documentation (there is already an appropriate appendix, IIRC). Generating the table will require about thiry seconds with "ls", so it hasn't seemed like an urgent priority to me, but certainly it must be done before release. regards, tom lane
Well, looking at Tom's nearby post I suspect putting the data into the database won't fly to far, so I guess we just need to update the docs and be sure to mention the potential compatability issue in the release notes. Robert Treat On Wednesday 14 July 2004 23:36, Bruce Momjian wrote: > Yes, I think you could argue this. In the old system, we could tell > them to look in the OS-supported timeszones. Now we have to tell them > to look in data/share/timezone. Is that enough or not? > > --------------------------------------------------------------------------- > > Robert Treat wrote: > > Can I argue that this is a missing implementation detail needed for 7.5? > > My grounds being that we are potentially breaking backwards compatability > > by changing the supported timezones but giving the user no easy way to > > determine just what is supported. > > > > Robert Treat > > > > On Wednesday 14 July 2004 18:25, Bruce Momjian wrote: > > > TODO? > > > > > > * Show supported times > > > > > > ----------------------------------------------------------------------- > > >---- > > > > > > Magnus Hagander wrote: > > > > >I am trying to figure out if there is a way to determine the > > > > > timezones supported in postgresql from within the database. If you > > > > > look at > > > > > http://www.postgresql.org/docs/7.4/static/datetime-keywords.html it > > > > > notes that time zone information is system dependent, (I interpret > > > > > to mean that anything I find in /usr/share/zoneinfo on linux should > > > > > be supported, can someone confirm that?) > > > > > > > > Yes, that should be it. > > > > > > > > > so how can an external app > > > > >determine which timezones are supported given that it could be > > > > > deployed against postgresql databases on different OS's. My > > > > > current thinking is that there is no way to get a complete list, > > > > > but perhaps the list of known timezones (as listed in the docs) are > > > > > available? If it is not, will this change in 7.5 now that we have > > > > > a standard timezone library we are using across platforms? > > > > > > > > In 7.5, you can check the files in <pgdir>/share/timezone. There is > > > > no function in the backend ATM to show them. I've been thinking of > > > > adding one (as a system view), but didn't get around to it before > > > > freeze. (It'd basically loop over the files in the directory) > > > > > > > > //Magnus > > > > > > > > ---------------------------(end of > > > > broadcast)--------------------------- TIP 6: Have you searched our > > > > list archives? > > > > > > > > http://archives.postgresql.org > > > > -- > > Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Brian K Boonstra wrote: > Let's say that I want to efficiently compute something like a weighted > standard deviation (the actual formula I have in mind is slightly more > complicated). The kind of SQL statement I want to have work is > something like > > SELECT weighted_stdev( t.val, t.weight ) FROM target_vals_tbl t > WHERE t.val > 0; > > > I thought I'd like to write a C function (or more properly a set of two > C functions) into the server side to handle this, and then declare it as > an aggregate using CREATE AGGREGATE. However, aggregate functions > appear to want just a single argument, so I feel like either I am on the > wrong track, or I have run into a limitation of postgresql. I'm not sure what the most elegant solution is, but when I've encountered this scenario in the past, I created a custom type for the aggregate. So this meant creating an input and output function for the type, and then creating a function to return the type for use in the aggregate. Example: CREATE OR REPLACE FUNCTION tier_input(cstring) RETURNS tier AS '/usr/local/pgsql/lib/tradedb.so' LANGUAGE 'C' WITH (isStrict); CREATE OR REPLACE FUNCTION tier_output(tier) RETURNS cstring AS '/usr/local/pgsql/lib/tradedb.so' LANGUAGE 'C' WITH (isStrict); CREATE TYPE tier ( INTERNALLENGTH = 136, INPUT = tier_input, OUTPUT = tier_output ); CREATE OR REPLACE FUNCTION to_tier(text, text, text, int4, int4) RETURNS tier AS '/usr/local/pgsql/lib/tradedb.so' LANGUAGE 'C' WITH (isStrict); CREATE OR REPLACE FUNCTION the_amount(tier) RETURNS text AS '/usr/local/pgsql/lib/tradedb.so' LANGUAGE 'C' WITH (isStrict); CREATE OR REPLACE FUNCTION tier_s(tier, tier) RETURNS tier AS '/usr/local/pgsql/lib/tradedb.so' LANGUAGE 'C' WITH (isStrict); CREATE OR REPLACE FUNCTION tier_f(tier) RETURNS tier AS '/usr/local/pgsql/lib/tradedb.so' LANGUAGE 'C' WITH (isStrict); CREATE AGGREGATE tier_sum ( BASETYPE = tier, SFUNC = tier_s, STYPE = tier, FINALFUNC = tier_f, INITCOND = '0 0 temp_table 0 0' ); And then I invoke the aggregate like: SELECT tier_sum(to_tier(a, b, c, d, e)) FROM foo WHERE bar; HTH, Mike Mascari
> Brian K Boonstra wrote:
>> ... aggregate functions
>> appear to want just a single argument, so I feel like either I am on the
>> wrong track, or I have run into a limitation of postgresql.
Sooner or later someone should fix aggregates to allow multiple inputs.
There was once a restriction in the catalog layout that prevented it,
but that's been gone since 7.3 or so. I think the only part that would
be even slightly difficult is supporting DISTINCT aggregates.
Mike Mascari <mascarm@mascari.com> writes:
> I'm not sure what the most elegant solution is, but when I've
> encountered this scenario in the past, I created a custom type for
> the aggregate.
This will actually be quite painless in 7.5, since you can just use a
composite type. It'd go something like
create type mytype as (f1 int, f2 text);
... create aggregate accepting mytype as input ...
select myagg(row(x,y)) from table;
No need for any C code.
regards, tom lane