Обсуждение: Library
A nice string concatenation aggregator in pg would be nice. Is there one in 8.4? I've been using array_agg but it's not quitethe same. Nor xml_agg. In any case I was wondering if our community has an on-line repository of shared code? I mean you can read the litt and seethe notes on building your own aggregator. But this one for example has been done soo many times already. I was thinkingthat there might be a library where we are sharing our .sql files of nice functions, triggers, etc, ... Anyone knowof such? Charlie
On 28 September 2010 11:21, Charles Holleran <scorpdaddy@hotmail.com> wrote: > A nice string concatenation aggregator in pg would be nice. Is there one in 8.4? I've been using array_agg but it's notquite the same. Nor xml_agg. > > In any case I was wondering if our community has an on-line repository of shared code? I mean you can read the litt andsee the notes on building your own aggregator. But this one for example has been done soo many times already. I was thinkingthat there might be a library where we are sharing our .sql files of nice functions, triggers, etc, ... Anyone knowof such? People place a lot of snippets on the wiki, but not exactly an organised set of functions: http://wiki.postgresql.org/wiki/Category:Library_Snippets The PostGresql eXtension Network (PGXN) is still in development: http://www.pgxn.org But as for a built-in string aggregate, that's only available from 9.0 onward. string_agg: http://www.postgresql.org/docs/9.0/static/functions-aggregate.html -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
Charles Holleran, 28.09.2010 12:21: > A nice string concatenation aggregator in pg would be nice. Is there > one in 8.4? I've been using array_agg but it's not quite the same. In 8.4 you can simply combine array_to_string() and and array_agg(): In 9.0 you can use string_agg() Thomas
Greetings! I have a table with a column declared to contain a timestamp with time zone. A database function inserts a row into this table using the following statement: insert into alarm_hold (charge, hold_code, hold_generated, condition_date) values (ChargeNum, '0471', 0, current_timestamp at time zone 'UTC'); The resulting value contained in the timestamp field is 2010-09-28 13:09:27.015-04. Since I am in the Eastern time zone of the United States and daylight savings time is in effect, the -04 indicates that it is 4 hours earlier for me than it is in Greenwich, England. And this value was generated at 9:09 this morning local time, so the 13:09 is understandable. The thing I am confused about is why the "-04" is there. This timestamp should show a UTC time. But with the -04 in there, it seems to me that it is showing an Eastern Daylight Time time. What don't I understand? Thanks very much! RobR
For me it seems that the time is shown using ISO-8601 format. Some reading on datetime and timestamps conversion: http://www.postgresql.org/docs/8.0/static/datatype-datetime.html http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT Hope that helps. Regards Luke 2010/9/28 Rob Richardson <Rob.Richardson@rad-con.com>: > Greetings! > > I have a table with a column declared to contain a timestamp with time > zone. A database function inserts a row into this table using the > following statement: > > insert into alarm_hold (charge, hold_code, hold_generated, > condition_date) values (ChargeNum, '0471', 0, current_timestamp at time > zone 'UTC'); > > The resulting value contained in the timestamp field is 2010-09-28 > 13:09:27.015-04. > > Since I am in the Eastern time zone of the United States and daylight > savings time is in effect, the -04 indicates that it is 4 hours earlier > for me than it is in Greenwich, England. And this value was generated > at 9:09 this morning local time, so the 13:09 is understandable. > > The thing I am confused about is why the "-04" is there. This timestamp > should show a UTC time. But with the -04 in there, it seems to me that > it is showing an Eastern Daylight Time time. > > What don't I understand? > > Thanks very much! > > RobR > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > -- Łukasz Brodziak II MU Bioinformatyka
What time zone does the value in my table represent to you? To me, it looks like an EDT time. It must be possible to compare the time in this table to a time stored in another table in a column whose type is timestamp without time zone. More specifics: My company makes annealing furnaces. Steel must be annealed (heated and then cooled at a controlled rate) to ensure that it won't crack when shaped into automobile bodies, applicances, fasteners, and so on. Various steps in the process are stored in the charge table in our database in pairs of fields: the local time and the UTC time. Both are of type timestamp without time zone. I need to be able to compare the time stored in the alarm_hold table (type timestamp with time zone) to the time stored in the charge table. I need to know if 2010-09-28 13:09:27.015-04 from a timestamp with time zone column represents the same time as 2010-09-28 13:09:27.015 from a timestamp without time zone column. Thanks again! RobR
On Tue, Sep 28, 2010 at 9:23 AM, Rob Richardson <Rob.Richardson@rad-con.com> wrote: > Greetings! > > I have a table with a column declared to contain a timestamp with time > zone. A database function inserts a row into this table using the > following statement: > > insert into alarm_hold (charge, hold_code, hold_generated, > condition_date) values (ChargeNum, '0471', 0, current_timestamp at time > zone 'UTC'); I don't think you should be using the "at time zone 'UTC'" part. Just use CURRENT_TIMESTAMP if you're inserting into a "timestamp with time zone" column, and let Postgres keep track of the time zone conversions for you. > The resulting value contained in the timestamp field is 2010-09-28 > 13:09:27.015-04. > > Since I am in the Eastern time zone of the United States and daylight > savings time is in effect, the -04 indicates that it is 4 hours earlier > for me than it is in Greenwich, England. And this value was generated > at 9:09 this morning local time, so the 13:09 is understandable. That value means "1:09 PM in Eastern time". The "-04" you see tells you that Postgres is giving you the time with a 4-hour offset from UTC, and is controlled by the "timezone" guc -- use "SHOW timezone;" to check. So I don't think that's what you want. Just use CURRENT_TIMESTAMP in your INSERTs and you should get what you expect. Josh
"Rob Richardson" <Rob.Richardson@rad-con.com> writes:
> I have a table with a column declared to contain a timestamp with time
> zone. A database function inserts a row into this table using the
> following statement:
> insert into alarm_hold (charge, hold_code, hold_generated,
> condition_date) values (ChargeNum, '0471', 0, current_timestamp at time
> zone 'UTC');
> The resulting value contained in the timestamp field is 2010-09-28
> 13:09:27.015-04.
> Since I am in the Eastern time zone of the United States and daylight
> savings time is in effect, the -04 indicates that it is 4 hours earlier
> for me than it is in Greenwich, England. And this value was generated
> at 9:09 this morning local time, so the 13:09 is understandable.
> The thing I am confused about is why the "-04" is there.
You declared the column as timestamp with time zone. The AT construct
produced a timestamp without time zone, viz "2010-09-28 13:09:27.015",
which the system then had to convert to timestamp with time zone; which
it did using the assumption that local time was meant.
Basically, you don't want to use AT TIME ZONE here; it loses information
to no good purpose. Just insert the result of current_timestamp and
call it good.
regards, tom lane
On Tue, Sep 28, 2010 at 11:00 AM, Rob Richardson <Rob.Richardson@rad-con.com> wrote: > I need to know if 2010-09-28 > 13:09:27.015-04 from a timestamp with time zone column represents the > same time as 2010-09-28 13:09:27.015 from a timestamp without time zone > column. To answer this question, try: SELECT '2010-09-28 13:09:27.015'::timestamp = '2010-09-28 13:09:27.015-04'::timestamp with time zone; You should see that Postgres assumes the plain timestamp is entered in a timezone which comes from the "timezone" GUC (again, use SHOW timezone; to see this). If you have your timezone set to US/Eastern, the above comparison will return true. If you have it set to something else, it should return false. If you use "timestamp with time zone" type everywhere, you'll avoid potential headaches of keeping track of timezones. Josh