[SQL] ISO time zone format

[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 for that.

TIA,
Sabin 



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

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

[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_id,
   log_date,
   log_uid,
   log_name,
   array_accum(trim(trailing ' ' from gh_name)) as groups
from (
   
select distinct on (log_rec_id, start_rec_id, fin_rec_id, gm_rec_id)
   log_rec_id,
   log_date,
   log_uid,
   log_name,
   start_rec_id,
   start_date,
   start_action,
   start_uid,
   start_name,
   fin_rec_id,
   fin_date,
   fin_action,
   fin_uid,
   fin_name,
   gm_rec_id,
   gm_date,
   gm_gid,
   gm_uid,
   gh.rec_id as gh_rec_id,
   gh.date as gh_date,
   gh.action as gh_action,
   gh.gid as gh_gid,
   gh.name as gh_name
from (
select *
from group_history
where action <> 1
) as gh
right join (
   
select log_rec_id,
   log_date,
   log_uid,
   log_name,
   start_rec_id,
   start_date,
   start_action,
   start_uid,
   start_name,
   fin_rec_id,
   fin_date,
   fin_action,
   fin_uid,
   fin_name,
   max(gm_rec_id) as gm_rec_id,
   max(gm_date) as gm_date,
   gm_gid,
   gm_uid
from (
   
select
   luid.*,
   gmh.rec_id as gm_rec_id,
   gmh.date as gm_date,
   gmh.gid as gm_gid,
   gmh.uid as gm_uid,
   gmh.action as gm_action
from group_member_history as gmh
right join (
   
select distinct on (cuh.log_rec_id, cuh.start_rec_id)
   cuh.*, duh.*
from (
select rec_id as fin_rec_id,
   date as fin_date,
   action as fin_action,
   uid as fin_uid,
   name as fin_name
from "user_history" 
where 1 = 1
and action <> 0
) as duh
right join (
select distinct on (log.rec_id)
   log.rec_id as log_rec_id,
   log.date as log_date,
   log.uid as log_uid,
   log.name as log_name,
   uh.rec_id as start_rec_id,
   uh.date as start_date,
   uh.action as start_action,
   uh.uid as start_uid,
   uh.name as start_name
from (
select *
from "user_history"
where 1 = 1
and action <> 1
order by date
) as uh
right join log_example_3 as log
on log.name = uh.name
and uh.date <= log.date
order by log.rec_id, start_date desc
) as cuh
on cuh.start_uid = duh.fin_uid
and duh.fin_date > cuh.start_date and duh.fin_date <= 
cuh.log_date
order by cuh.log_rec_id, cuh.start_rec_id, duh.fin_rec_id
   
) as luid
on gmh.uid = luid.start_uid
and gmh.date <= luid.log_date
   
) as lgm
group by
   log_rec_id,
   log_date,
   log_uid,
   log_name,
   start_rec_id,
   start_date,
   start_action,
   start_uid,
   start_name,
   fin_rec_id,
   fin_date,
   fin_action,
   fin_uid,
   fin_name,
   gm_gid,
   gm_uid
having count(gm_action) % 2 = 1 or count(gm_action) = 0
order by log_date
   
) as lgm
on gh.gid = lgm.gm_gid
and gh.date <= lgm.log_date
order by log_rec_id, start_rec_id, fin_rec_id, gm_rec_id, gh.date desc
   
) as lgs
group by log_rec_id,
   log_date,
   log_uid,
   log_name
order by log_date;

Re: [SQL] ISO time zone format

[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, excepting the numeric ISO
> time zone.
>
> Please suggest the appropiate pattern for that.

Have you tried the default PgSQL behaviour, without setting DateStyle, or 
doing any explicit formating?
Maybe its exactly what you want.
What does 
select now();
from within psql, output in your system?
>
> TIA,
> Sabin
>
>
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match

-- 
Achilleas Mantzios

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] ISO time zone format

[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 the appropiate pattern for that.


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?

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

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

[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
   where t.family = test_table.family
   and t.rang = 1
   and table.rang=0
   -- perform the updte only when required
   and mygroup <> t.mygroup;

But when you are dealing with  "parent - child" relations within a
single table as in my case, 
a single table scan with SELECT DISTINCT ON  and a row by row comparison
on the result set appears to be faster.

I tested both approaches on tables with ca. 14'000'000 rows where 25% of
them needed to be updated.

The above update statement run in 5H30' where my function did the job in
2H.
(as my tables are very large, much time is lost in i/o wait)



Cheers,

Marc



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[SQL] tsearch2 query question

[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_tsvector(subtitle) @@ q )

ORDER BY Score



I have such a query. I'm not very sure if it will work but that's not 

the part of the question. As you see I'm using a lot to_tsvector() 

function. Which I believe it will not be good for the performance. So I 

thought changing my query to something like this:



SELECT contentid, title, (rank(fts_body, q) + rank(fts_title,q) + 

rank(fts_subtitle,q) ) AS Score

FROM content,

   to_tsquery('search & string') AS q,

   to_tsvector(body) AS fts_body,

   to_tsvector(title) AS fts_title,

   to_tsvector(subtitle) AS fts_subtitle

WHERE statusid = 1

AND ispublished = 1

AND ( fts_body @@ q

   OR fts_title @@ q

   OR fts_subtitle @@ q )

ORDER BY Score



So when I have changed to this, will the to_tsvector part will be 

available for every row? Or will it be just computed once? I mean in the 

first query where part is executed for every row, so I'm sure that it 

will be evaluated for all the rows. But when I put that in the from part 

will it compute the value once and will use the same value for all the 

where clauses? If that's the case what will be the value of fts_body? 

The tsvector of every row's data or just one row's data?



thank you for your time and patience



roy simkes




_
Invite your mail contacts to join your friends list with Windows Live Spaces. 
It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us

Re: [SQL] ISO time zone format

[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( 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 the ISO time zone (numeric).

Regards,
Sabin




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] ISO time zone format

[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 the ISO time zone (numeric).


Hmm - we don't seem to support those codes (TZH, TZM) for some reason.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#sthref405

But it seems the developers know about it:
  http://www.postgresql.org/docs/faqs.TODO.html
  http://archives.postgresql.org/pgsql-hackers/2005-12/msg00948.php

If you know a little bit of "C" then you should be able to submit a 
patch for this without too much trouble.



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');


HTH
--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] recursive query

[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, district, and location.

I am trying to make it more flexible some companies don't use 4 levels some
want more, some have different sets of users and would like their own
hierarchy.

My idea is to have a virtual hierarchy (all share the location record which
is the lowest level and a separate file that will be linked with a table
that has the locationid and the hierarchy id to enable multiple hierarchy).

 I did a bit of research and found some code to do recursive sql (but it was
IBM and I am not sure I even got it right for postgres).

 

create or replace temp view test (clientnum,id ,parentid,descr, level) as (

select   h.clientnum,h.id ,h.parentid,h.descr,0 as level

 from tblhyerarchy h where parentid =0 

union all

select   h1.clientnum,h1.id ,h1.parentid,h1.descr,h.level +1 as level

 from tblhyerarchy h1

inner join test h on h1.parentid =h.id 

where h.level < 5

);

select * from test

 

but get a 

ERROR: infinite recursion detected in rules for relation "test" SQL state:
42P17

 

I am basing on a table created with

CREATE TABLE tblhyerarchy

(

  clientnum character varying(16) NOT NULL,

  id integer NOT NULL,

  parentid integer NOT NULL,

  descr character varying(250),

  CONSTRAINT pk_tblhyerarchy PRIMARY KEY (clientnum, id)

) 

WITH OIDS;

 

 

Any help is much appreciated. I am also a little confused how to do a
crosstab on the output.

In the end I will want to get at a data record that has the location id and
then link to the hierarchy with each level of the hierarchy present for
aggregation etc.

 

Joel Fradkin

 

Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305

 

  [EMAIL PROTECTED]
  www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
 This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information.  Any unauthorized review,
use, disclosure or distribution is prohibited.  If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.

 

Re: [SQL] ISO time zone format

[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 through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly