[SQL] ISO time zone format

2007-09-05 Thread Sabin Coanda
Hi there, I'd like to format a time stamp with time zone as ISO pattern (e.g. 1999-01-08 04:05:06 -8:00) I found the patterns for all the timestamp parts, from "Data Type Formatting Functions" Postgresql documentation, excepting the numeric ISO time zone. Please suggest the appropiate pattern

Re: [SQL] 8.0.1 to 8.0.13 upgrade added 15% lack of time of query execution

2007-09-05 Thread Aleksandr Vinokurov
Sorry, just noticed that both query plans are actually for the 8.0.13 release. This one is right: "Total runtime: 93866.526 ms" Any suggestions? Please. Aleksandr Vinokurov wrote: Both plans with queries echoed are attached to this email. Timing is on. explain analyze select log_rec

Re: [SQL] ISO time zone format

2007-09-05 Thread Achilleas Mantzios
Στις Τετάρτη 05 Σεπτέμβριος 2007 10:30, ο/η Sabin Coanda έγραψε: > Hi there, > > I'd like to format a time stamp with time zone as ISO pattern (e.g. > 1999-01-08 04:05:06 -8:00) > > I found the patterns for all the timestamp parts, from "Data Type > Formatting Functions" Postgresql documentation, e

Re: [SQL] ISO time zone format

2007-09-05 Thread Richard Huxton
Sabin Coanda wrote: Hi there, I'd like to format a time stamp with time zone as ISO pattern (e.g. 1999-01-08 04:05:06 -8:00) I found the patterns for all the timestamp parts, from "Data Type Formatting Functions" Postgresql documentation, excepting the numeric ISO time zone. Please suggest

Re: [SQL] EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?

2007-09-05 Thread Marc Mamin
Hello Dirk, I have to disagree. Your first update query is very low. It probably implies to run the sub select statement for each row to be updated. Following update statement is already much faster: (using UPDATE FROM) update test_table set mygroup= t.mygroup from test_table as t

[SQL] tsearch2 query question

2007-09-05 Thread roy simkes
Hi, SELECT contentid, title, (rank(to_tsvector(body),q) + rank(to_tsvector(title),q) + rank(to_tsvector(subtitle),q)) AS Score FROM content, to_tsquery('parkyeri') AS q WHERE statusid = 1 AND ispublished = 1 AND (to_tsvector(body) @@ q OR to_tsvector(title) @@ q OR to_tsve

Re: [SQL] ISO time zone format

2007-09-05 Thread Sabin Coanda
> > If your DateStyle is set to ISO that's the default format. You don't need > to use to_char() > > Or am I missing your point? > Hi Richard, I'd just like to format it independent of my DateStyle. Some timestamp parts may be formatted this way. For instance I can format ISO date with to_char

Re: [SQL] ISO time zone format

2007-09-05 Thread Richard Huxton
Sabin Coanda wrote: I'd just like to format it independent of my DateStyle. Some timestamp parts may be formatted this way. For instance I can format ISO date with to_char( dt, '-MM-DD'), and ISO time with to_char( dt, 'HH24:MI:SS') as well. I'd just like to find the format pattern for

[SQL] recursive query

2007-09-05 Thread Joel Fradkin
Hi, I am trying to do a hierarchy design. We currently have it hard coded to 4 levels and the location table has an id field for each level where the data has a location id. This allows easy access by level (to restrict who see what) and easy to arrogate for reporting by division, region, distric

Re: [SQL] ISO time zone format

2007-09-05 Thread Sabin Coanda
> > For the moment, you'll have to use extract() to pick out the bit(s) you > need: > to_char(extract(timezone_hour from current_timestamp), 'S00'); > It's perfect for me, thanks a lot. Sabin ---(end of broadcast)--- TIP 1: if posting/reading t