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?)

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