Re: [SQL] Date trunc in UTC
The biggest point that I see is that it would be nice to have some kind
of function that works with UTC values, regarding of which timezone the
user has set. Let's say, something like
SELECT UTC_DATE_TRUNC ('month', NOW ());
utc_date_trunc
2002-11-01 01:00:00+01
because if you work with international applications, the beggining of
the month in Spain should be the same as in Australia. But everyone will
see it in its own timezone.
I think that it would be also interesting to have the UTCeed versions of
EXTRACT and AGE.
Tom Lane wrote:
Richard Huxton <[EMAIL PROTECTED]> writes:
Hmm - good point. You can revert to the client default but not to the
previous value. I don't know of any way to read these SET values
either - a quick poke through pg_proc didn't show anything likely.
In 7.3 you can use current_setting() and set_config() to access SHOW/SET
functionality. However, I agree with your suggestion of AT TIME ZONE
to rotate a timestamp into a target timezone, rather than mucking with
the TimeZone setting.
BTW, Thomas: is AT TIME ZONE supposed to accept
timestamp-without-timezone input? If so, what's it supposed to do with
it? The current behavior seems unintuitive to say the least:
regression=# select now();
now
---
2002-11-21 10:19:14.591001-05
(1 row)
regression=# select now() at time zone 'UTC';
timezone
2002-11-21 15:19:18.588279
(1 row)
regression=# select localtimestamp;
timestamp
2002-11-21 10:19:22.629865
(1 row)
regression=# select localtimestamp at time zone 'UTC';
timezone
---
2002-11-21 05:19:26.178861-05
(1 row)
It seems to me that the last case should give either an error or
2002-11-21 15:19:26.178861 (ie, assume that the timestamp without time
zone is in my TimeZone zone). In any case, surely the result should
be of type timestamp WITHOUT time zone?
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] connectby questions
On Fri, 22 Nov 2002, Dan Langille wrote:
> Here is the actual data for the above nodes:
>
> freshports=# select * from element where id in (104503, 104504, 104505);
>id | name | parent_id | directory_file_flag | status
> +--+---+-+
> 104503 | multimedia | 77344 | D | A
> 104504 | Makefile |104503 | F | A
> 104505 | chapter.sgml |104503 | F | A
> (3 rows)
>
> What I would like to include in the output is all of the above fields.
DOH! Use a join stupid!
freshports=# select t.*, tt.name
freshports-# from connectby('simple_element', 'id', 'parent_id', '104503',
0, '/')
freshports-# as t(id int, parent_id int, level int, branch text),
element tt
freshports-# where t.id = tt.id;
id | parent_id | level |branch | name
+---+---+---+--
104503 | | 0 | 104503| multimedia
104504 |104503 | 1 | 104503/104504 | Makefile
104505 |104503 | 1 | 104503/104505 | chapter.sgml
(3 rows)
Ok, that works. But I have two issues:
1 - speed: That join takes 7 seconds:
freshports=# explain analyze
freshports-# select t.*, tt.name
freshports-# from connectby('simple_element', 'id', 'parent_id', '104503',
0, '/
')
freshports-# as t(id int, parent_id int, level int, branch text),
elemen
t tt
freshports-# where t.id = tt.id;
QUERY
PLAN
--
Merge Join (cost=62.33..3050.43 rows=1000 width=60) (actual
time=7420.23..7421
.03 rows=3 loops=1)
Merge Cond: ("outer".id = "inner".id)
-> Index Scan using element_pkey on element tt (cost=0.00..2708.97
rows=104
649 width=16) (actual time=1.69..5933.32 rows=104505 loops=1)
-> Sort (cost=62.33..64.83 rows=1000 width=44) (actual
time=10.84..10.87 ro
ws=3 loops=1)
Sort Key: t.id
-> Function Scan on connectby t (cost=0.00..12.50 rows=1000
width=44)
(actual time=10.12..10.17 rows=3 loops=1)
Total runtime: 7421.78 msec
(7 rows)
freshports=#
2 - What I really want in the output is the branch defined by the name
fields, not by the id fields (e.g. instead of 104503/104504, show
multimedia/Makefile.
For what its worth, I did populate my test database with the full pathname
field, maintained by triggers. However, the initial population of that
data took 160 minutes... Luckily, the tiggers are there as constraints (of
a sort) rather than actually used to cascade changes. In practice, nodes
do not get renamed in my application.
Cheers
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] calculating interval
Dear All, Is there any way in postgresql to calculate the interval between two times excluding specific days,specific duration. Ex: timestamp1 : 2002-10-01 10:30AM timestamp2 : 2002-15-01 04:50PM suppose if i need the difference between timestamp1,timestamp2 but i don't want to count how many sun days are coming, i want to ignore all sundays in between,i want to ignore certain timings(say 10.00 AM to 5:00PM)s also,then how can I get the duration in this way. Thanks and Regards, Praveen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] calculating interval
On 22 Nov 2002, praveen vejandla wrote: > Dear All, > > Is there any way in postgresql to calculate the interval between > two times excluding specific days,specific duration. > > Ex: > timestamp1 : 2002-10-01 10:30AM > timestamp2 : 2002-15-01 04:50PM > > suppose if i need the difference between timestamp1,timestamp2 but > i don't want to count how many sun days are coming, i want to > ignore all sundays in between,i want to ignore certain timings(say > 10.00 AM to 5:00PM)s also,then how can I get the duration in this > way. My guess: write a function. Calculating the number of days between the two dates is easy. To avoid certain days, of the week, in your case, Sunday, I would count the number of whole weeks between the two dates. test=# select '2002-10-01 10:30AM'::timestamp - '2002-15-01 04:50PM'::timestamp; ?column? 258 days 16:40 In this case 258/7 = 36.85... So you know you have 36 Sundays in there. This will need adjusting for non-full weeks. Hope that gets you started. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Date trunc in UTC
Thrasher <[EMAIL PROTECTED]> writes:
> The biggest point that I see is that it would be nice to have some kind
> of function that works with UTC values, regarding of which timezone the
> user has set.
You can do that in 7.3, using the AT TIME ZONE construct. Observe:
regression-# begin;
BEGIN
regression=# select now();
now
---
2002-11-22 09:59:48.706508-05 'now' in local time (EST)
(1 row)
regression=# select now() at time zone 'UTC';
timezone
2002-11-22 14:59:48.706508 'now' in UTC
(1 row)
regression=# select date_trunc('month', now() at time zone 'UTC');
date_trunc
-
2002-11-01 00:00:00month start in UTC
(1 row)
regression=# select date_trunc('month', now() at time zone 'UTC') at time zone 'UTC';
timezone
2002-10-31 19:00:00-05 month start in local time
(1 row)
regression=# commit;
This is a tad bulky maybe, but it'd be easy to wrap up in a user-defined
function.
AT TIME ZONE was less functional, and very poorly documented, in 7.2.
The 7.3 version is described at
http://developer.postgresql.org/docs/postgres/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] calculating interval
Watch out! 36.85 weeks could have 37 sundays... Take into account the day of the week of the first and last day. Also, process the first and last day separately and work with whole day, if you want to exclude part of the day. Dan Langille wrote: > > On 22 Nov 2002, praveen vejandla wrote: > > > Dear All, > > > > Is there any way in postgresql to calculate the interval between > > two times excluding specific days,specific duration. > > > > Ex: > > timestamp1 : 2002-10-01 10:30AM > > timestamp2 : 2002-15-01 04:50PM > > > > suppose if i need the difference between timestamp1,timestamp2 but > > i don't want to count how many sun days are coming, i want to > > ignore all sundays in between,i want to ignore certain timings(say > > 10.00 AM to 5:00PM)s also,then how can I get the duration in this > > way. > > My guess: write a function. Calculating the number of days between the > two dates is easy. To avoid certain days, of the week, in your case, > Sunday, I would count the number of whole weeks between the two dates. > > test=# select '2002-10-01 10:30AM'::timestamp - '2002-15-01 > 04:50PM'::timestamp; > ?column? > > 258 days 16:40 > > In this case 258/7 = 36.85... So you know you have 36 Sundays in there. > This will need adjusting for non-full weeks. > > Hope that gets you started. > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] calculating interval
On Fri, 22 Nov 2002 09:26:31 -0500, praveen vejandla wrote: > Dear All, > > Is there any way in postgresql to calculate the interval between two > times excluding specific days,specific duration. > > Ex: > timestamp1 : 2002-10-01 10:30AM > timestamp2 : 2002-15-01 04:50PM > > suppose if i need the difference between timestamp1,timestamp2 but i > don't want to count how many sun days are coming, i want to ignore all > sundays in between,i want to ignore certain timings(say 10.00 AM to > 5:00PM)s also,then how can I get the duration in this way. > The easiest way would be to write a function in a language that supports some kind of date library. I'm most familiar with Perl and its Date::Manip library, so that's what I would turn to. -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(end of broadcast)--- TIP 3: 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] escape single quote in INSERT command
On Fri, 22 Nov 2002 08:00:01 -0500, Hunter wrote: > I have a perl application for a registration form. I'd like to put > escape characters in my insert command to accommodate for ' (i.e. > O'Brien, O'Malley, etc). I've tired double quotes, single quotes, back > tick, forward ticks, curly bracket, round brackets - no success. Are you using the DBI interface? That's the most straightforward way to accomplish this. Something like this: use strict; use DBI; my $dbh = DBI->new(...see docs for details...); $dbh->do(q|INSERT INTO mytable (col1) VALUES (?)|, undef, q|O'Malley|) or die $DBI::errstr; $dbh->commit; By using the '?' placeholder format for statements, you can pass in any data without having to worry about quoting. -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Trees: maintaining pathnames
On 20 Nov 2002 at 15:20, Dan Langille wrote: > On 17 Nov 2002 at 14:51, Josh Berkus wrote: > > > Dan, > > > > > My existing tree implementation reflects the files contained on > > > disk. > > > The > > > full pathname to a particlar file is obtained from the path to the > > > parent directory. I am now considering putting this information > > > into a field in the table. > > > > > Suggestions, comment, open ridicule, most welcome. thanks. > > > > This is a fine implementation using the adjacency list model of tree > > design. However, I think you may find that the string-based tree > > implementation in /contrib/ltree is more suited to your purposes, > > and easier to maintain. > > That looks interesting. I have installed that onto a test server and > I'm playing around with it. FWIW, the ltree seems to implement a tree through text manipulation. I already have a tree (using a sinble table with id, parent_id). Therefore, I think ltree is not an option in this situation. My creation of the pathname was to save processing time. I'll talk more about that in my next post. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Trees: maintaining pathnames
On 17 Nov 2002 at 11:39, Dan Langille wrote: > My existing tree implementation reflects the files contained on disk. > The full pathname to a particlar file is obtained from the path to the > parent directory. I am now considering putting this information into > a field in the table. > > Attached you will find the pg_dump from my test database (2.4k) if you > want to test with this setup and in case what I have pasted below > contains an error. > > Here is the table and the test data: > > create table tree(id int not null, parent_id int, name text not null, > pathname text not null, primary key (id)); > > insert into tree (id, name, pathname) values (1, 'usr', '/usr'); > insert into tree (id, name, parent_id, pathname) values (2, 'ports', > 1, '/usr/ports'); insert into tree values (3, 2, 'security', 'test'); > > select * from tree; > > test=# select * from tree; > id | parent_id | name | pathname > +---+--+- > 1 | | usr | /usr > 2 | 1 | ports| /usr/ports > 3 | 2 | security | /usr/ports/security > (3 rows) > > > The goal is to ensure that pathname always contains the correct value. I am now trying another method, which involves the use of a cache table. In short, we store the pathname in another table. create table tree_pathnames ( id int4 not null, pathname text not null, primary key(id), foreign key (id) references tree(id) on delete cascade on update cascade ); I populated this table with the following: insert into tree_pathnames select id, pathname from tree; My next task was to create a function which would cascade a change to tree.name throughout tree_pathname. Here is what I came up with: create or replace function tree_pathname_set_children(int4, text) returns int as 'DECLARE node ALIAS for $1; path ALIAS for $2; children record; BEGIN FOR children IN SELECT ep.id, ep.pathname, e.name FROM element_pathnames ep, element e WHERE ep.id = e.id AND e.parent_id = node LOOP -- children.pathname = path || ''/'' || children.name; RAISE NOTICE ''in tree_pathname_set_children %/%'', path, children.name ; UPDATE element_pathnames set pathname = path || ''/'' || children.name where id = children.id; perform tree_pathname_set_children(children.id, path || ''/'' || children.name); END LOOP; return 0; END;' language 'plpgsql'; This function is invoked from within the trigger on tree: create or replace function tree_pathnames() returns opaque as ' DECLARE parent_pathname text; my_pathname text; BEGIN if old.name <> new.name then select pathname into parent_pathname from tree_pathnames where id = new.parent_id; if found then my_pathname = parent_pathname || \'/\' ||new.name; else my_pathname = \'/\' || new.name; end if; new.pathname = my_pathname; update tree_pathnames set pathname = my_pathname where id = new.id; perform tree_pathname_set_children(new.id,my_pathname); end if; RETURN new; END;' language 'plpgsql'; drop trigger tree_pathnames on element; create trigger tree_pathnames before update on element for each row execute procedure tree_pathnames(); I have done only preliminary testing on this, but it seems to work fine for my application. Comments please. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Trees: maintaining pathnames
Dan, Looks good to me. It's the same thing I do for the Celko tree structures in one application -- I have a cache table holding such things as level and parent_id for each node, values which can only be generated from the source tables through slow aggregates. Also, the use of a child table to hold the pathnames should cure your "cascading trigger" problem. -Josh Berkus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
