Re: [SQL] max timestamp
Hi Michael, Try this.. ace=> create table test( mytime timestamp ); CREATE ace=> insert into test values (now() ); INSERT 1823542 1 ace=> insert into test values (now() ); INSERT 1823543 1 ace=> insert into test values (now()-1); INSERT 1823544 1 ace=> insert into test values (now()-1); INSERT 1823545 1 ace=> insert into test values (now()-2); INSERT 1823546 1 ace=> select * from test; mytime -- 2004-02-16 14:27:15.936368+05:30 2004-02-16 14:27:20.888205+05:30 2004-02-15 00:00:00+05:30 2004-02-15 00:00:00+05:30 2004-02-14 00:00:00+05:30 (5 rows) ace=> select to_char(mytime,'dd-mm-'),max(mytime) from test group by 1; to_char | max +-- 14-02-2004 | 2004-02-14 00:00:00+05:30 15-02-2004 | 2004-02-15 00:00:00+05:30 16-02-2004 | 2004-02-16 14:27:20.888205+05:30 (3 rows) HTH Thanx Denis - Original Message - From: "Michael Sterling" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, February 10, 2004 11:44 PM Subject: [SQL] max timestamp > i'm trying to get the max time stamp, from each day, of a range of > dates, not just the max time stamp for the complete range dates but > for each day. > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Getting the week of a date
Dear Friends,
Postgres 7.3.4 on RH Linux7.2.
While this works for month and why not for
week
test=# select date_trunc('month',current_date + ('5
month')::INTERVAL);
date_trunc- 2004-07-01 00:00:00(1
row)
test=# select date_trunc('week',current_date + ('5
month')::INTERVAL);ERROR: TIMESTAMP units 'week' not
supportedtest=#
Any idea on how to find the 3 rd Wednesday of any given month.
Thanks
Kumar
Re: [SQL] Getting the week of a date
When grilled further on (Mon, 16 Feb 2004 17:40:08 +0530), "Kumar" <[EMAIL PROTECTED]> confessed: > Dear Friends, > > Postgres 7.3.4 on RH Linux7.2. > > While this works for month and why not for week > date_trunc (obviously) doesn't support week. I ran into this a while ago, and came up with this function. I left the function signature the same as date_trunc, even though I don't use the first argument. I did only minor testing (10 years or so), so no guarantee about it's correctness. And it's kind of slow... CREATE OR REPLACE FUNCTION date_trunc_week( text, timestamp ) RETURNS timestamp AS ' DECLARE reading_time ALIAS FOR $2; year timestamp; dow integer; adjust text; week text; BEGIN year := date_trunc( ''year''::text, reading_time ); week := date_part( ''week'', reading_time ) - 1 || '' week''; dow := date_part( ''dow'', year ); -- If the dow is less than Thursday, then the start week is last year IF dow <= 4 THEN adjust := 1 - dow || '' day''; ELSE adjust := 8 - dow || '' day''; END IF; RETURN year + adjust::interval + week::interval; END; ' LANGUAGE plpgsql IMMUTABLE STRICT; -- 05:37:49 up 1 day, 13:20, 2 users, load average: 0.09, 0.36, 0.63 Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003 pgp0.pgp Description: PGP signature
Re: [SQL] Getting the week of a date
On Monday 16 February 2004 15:10, you wrote:
>
> Any idea on how to find the 3 rd Wednesday of any given month.
SELECT
1-(to_char(date_trunc('month', now()::timestamp),'D'))::INT2 + 7*3-3
replace now with any date and you'll the the day number of a third Wed in that
month.
7 is a constant (factor 3 is a desired week number)
-3 is number of days to step back from sunday to a desired day of week
(-3 stands for Wed)
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[SQL] Date format problems
Hi im using the function below to insert data into my db; im using now() to get the timestamptz, however when inserted in the db the format seems to vary, the majority of the time its in the required European style but does spontaniously change to various other type can anyone throw any light on this problem. Further info: DATESTYLE is currently set to European. db table type is 'timestamptz' ### CREATE FUNCTION newmess(int4, text, varchar) RETURNS varchar AS ' DECLARE userid ALIAS for $1; message ALIAS for $2; touser ALIAS for $3; enttime DATETIME; touserid INTEGER; rdset BIT; from VARCHAR; BEGIN rdset = 0; touserid=(select id from users where lastname=touser); enttime=(select now()); from=(select lastname from users where id = userid); INSERT INTO CallLog.message(message, fromuser, touser, txtime, rd, fromusern) values(message. userid, touserid, enttime, rdset, from); END; ' LANGUAGE 'plpgsql'; * Im getting desperate, please help if you can, and thx to those that replied to my previous mail. Many Thanks in advance, Kind Regards, Mark. ___ Disclaimer: Great Ormond Street Hospital for Children NHS Trust SECURITY WARNING RE: PATIENT OR OTHER CONFIDENTIAL DATA. Please note that Internet E-mail is simply not a secure communication medium. We strongly advise that you understand & observe this lack of security when e-mailing us. This email and any files transmitted with it are intended solely for the use of the individual to whom they are addressed. If you have received this email in error please notify your email administrator. Any views or opinions are solely those of the author of this email and do not represent those of Great Ormond Street Hospital for Children NHS Trust unless specifically stated. VIRUSES: This email message has been checked for the presence of computer viruses by Sophos antivirus software. However, this does not guarantee that this email is free of viruses, and the recipient should perform their own check. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Getting the week of a date
On Monday 16 February 2004 12:10, Kumar wrote:
>
> test=# select date_trunc('week',current_date + ('5 month')::INTERVAL);
> ERROR: TIMESTAMP units 'week' not supported
Try EXTRACT(week FROM ...)
--
Richard Huxton
Archonet Ltd
---(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] Getting the week of a date
EXCUSE ME, GUYS !
i forgot to add one monome:
7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4)
which is stands for skip a first week of month in case it is not consist Wed
finally the select will be similar the following
SELECT 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4) + 1 -
(to_char(date_trunc('month',now()),'D'))::INT2 + 7*3-3 ;
4 - is a number of Wed in a week (in postgresql numeration)
---(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] Date format problems
"Mark Roberts" <[EMAIL PROTECTED]> writes: > Hi im using the function below to insert data into my db; im using > now() to get the timestamptz, however when inserted in the db the format > seems to vary, the majority of the time its in the required European > style but does spontaniously change to various other type can anyone > throw any light on this problem. This is way too vague for anyone to help. What PG version are you using? What is the actual datatype of the column you're inserting into? Can you provide a specific example of a misformatted data value? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Tip: a function for creating a remote view using dblink
Hello,
I'm posting a function here in the hope others may find it useful
and/or correct my mistakes/make improvements :)
This creates a view of a remote table, using dblink:
CREATE OR REPLACE FUNCTION dblink_create_view(text, text, text)
RETURNS VOID
LANGUAGE plpgsql
STRICT
AS '
DECLARE
connstr ALIAS FOR $1;
remote_name ALIAS FOR $2;
local_name ALIAS FOR $3;
schema_name text;
table_name text;
rec RECORD;
col_names text := ;
col_defstext := ;
sql_str text;
BEGIN
schema_name := split_part(remote_name, ''.'', 1);
table_name := split_part(remote_name, ''.'', 2);
FOR rec IN
SELECT * FROM dblink(connstr,
''SELECT
a.attname,
format_type(a.atttypid, a.atttypmod)
FROM
pg_catalog.pg_class c INNER JOIN
pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) INNER JOIN
pg_catalog.pg_attribute a ON (a.attrelid = c.oid)
WHERE
n.nspname = '' || quote_literal(schema_name) || '' AND
c.relname = '' || quote_literal(table_name) || '' AND
a.attisdropped = false AND
a.attnum > 0'')
AS rel (n name, t text)
LOOP
col_names := col_names || quote_ident(rec.n) || '','';
col_defs := col_defs || quote_ident(rec.n) || '' '' || rec.t || '','';
END LOOP;
sql_str := ''CREATE VIEW '' || local_name ||
'' AS SELECT * FROM dblink('' || quote_literal(connstr) || '','' ||
quote_literal(''SELECT '' || trim(trailing '','' from col_names) ||
'' FROM '' || quote_ident(schema_name) || ''.'' ||
quote_ident(table_name)) ||
'') AS rel ('' || trim(trailing '','' from col_defs) || '')'';
EXECUTE sql_str;
RETURN;
END
';
Usage example:
SELECT dblink_create_view('host=... dbname=... user=...',
'schema.remote_table', 'local_view');
SELECT * FROM local_view;
The schema MUST be specified for the remote table name.
Suggestions for improvement welcome. Any ideas?
Is there any existing site (a wiki for example) for posting PostgreSQL
specific tips?
(Wasn't sure if pgsql-sql is the right place for this kind of thing)
--
Mark Gibson
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(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] Date format problems
Sure, sorry; Im using postgres version 7.2.1, and the column data type is 'timestamptz' Data examples: 13/02/04 12:35:27 appears in the column as 02/04/13 12:35:27, or 13/04/02 12:35:27 70% of the time it is inserted in the correct format. The function shown in previous email is called by a C++ Builder 5 program using the postgresSQL ODBC driver version 7.02.00.05 (Insight Distribution Systems) Any other info required ? Sorry for the stupid questions but im a bit of a n00b, no excuse I guess, but I just cant figure out whats going on. Thanks for all your efforts, Kind Regards, Mark. >>> Tom Lane <[EMAIL PROTECTED]> 02/16/04 03:34pm >>> "Mark Roberts" <[EMAIL PROTECTED]> writes: > Hi im using the function below to insert data into my db; im using > now() to get the timestamptz, however when inserted in the db the format > seems to vary, the majority of the time its in the required European > style but does spontaniously change to various other type can anyone > throw any light on this problem. This is way too vague for anyone to help. What PG version are you using? What is the actual datatype of the column you're inserting into? Can you provide a specific example of a misformatted data value? regards, tom lane ___ Disclaimer: Great Ormond Street Hospital for Children NHS Trust SECURITY WARNING RE: PATIENT OR OTHER CONFIDENTIAL DATA. Please note that Internet E-mail is simply not a secure communication medium. We strongly advise that you understand & observe this lack of security when e-mailing us. This email and any files transmitted with it are intended solely for the use of the individual to whom they are addressed. If you have received this email in error please notify your email administrator. Any views or opinions are solely those of the author of this email and do not represent those of Great Ormond Street Hospital for Children NHS Trust unless specifically stated. VIRUSES: This email message has been checked for the presence of computer viruses by Sophos antivirus software. However, this does not guarantee that this email is free of viruses, and the recipient should perform their own check. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Date format problems
"Mark Roberts" <[EMAIL PROTECTED]> writes: > Sure, sorry; Im using postgres version 7.2.1, and the column data type > is 'timestamptz' > Data examples: > 13/02/04 12:35:27 appears in the column as 02/04/13 12:35:27, or > 13/04/02 12:35:27 > 70% of the time it is inserted in the correct format. Well, part of your confusion might be due to the assumption that it's an insertion problem; that's not evident from what you've said. The stored representation of timestamptz is an absolute time count (seconds before or after midnight 1/1/2000, IIRC) and there is no possibility of "wrong field order" there. So your problem is either that the timestamp input converter interprets an ambiguous string incorrectly, or that the output formatter is presenting the broken-down time in a format other than the one you want. In the particular example you gave (a plpgsql function inserting the value of now()) into a table), I do not believe that the timestamptz value returned from now() will ever get converted to textual form at all, so it's not possible for an input interpretation error to occur. What will hit disk is exactly the same time count now() gave back. So my bet at this point is that what you have got is a variation in output formatting style, and the only possible way for that to happen is if you're not consistently setting the same DateStyle. In recent releases you can set DateStyle in postgresql.conf, but I think in 7.2 that did not work and you had to do something else to establish a system-wide default for DateStyle. Check the manual about runtime configuration settings and postmaster switches. If you have some entries in the column that are inserted by means other than this plpgsql function, then it could be that you've got input interpretation issues for those entries. Again the answer is most likely that you're not setting DateStyle consistently. BTW, DateStyle is really two separate variables, one that controls output format and one that controls the presumed order of MM,DD,YY fields when the input is ambiguous. (Ugly, I know ... it got that way for historical reasons ...) Be sure you are setting both parts. 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] Function
On Mon, 16 Feb 2004, Sumita Biswas (sbiswas) wrote: > Thanks for the answer. > I have one more issue. How do I test a function that I wrote? > I was able to create a function called Proc_ConferenceSummary(). > In SQL Server I used to run it through query analyzer by writing the > following command: > exec Proc_ConferenceSummary '12/1/2003','1/23/2004',1,1,0,5001 For functions that return setof or complex types, you can do it as select * from Proc_ConferenceSummary(...) as alias; For simple functions you can just use: select functionname(...); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Getting the week of a date
Seems a part of your function always returns '0'
select 1 - (to_char(date_trunc('month',now()),'D'))::INT2
because while we use date_trunc it will always return the first day of the
month and when it get subtracted by '1' it be always zero. Is there any
reason why you have included that?
Thanks
Kumar
- Original Message -
From: "sad" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, February 16, 2004 6:53 PM
Subject: Re: [SQL] Getting the week of a date
EXCUSE ME, GUYS !
i forgot to add one monome:
7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4)
which is stands for skip a first week of month in case it is not consist Wed
finally the select will be similar the following
SELECT 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4) + 1 -
(to_char(date_trunc('month',now()),'D'))::INT2 + 7*3-3 ;
4 - is a number of Wed in a week (in postgresql numeration)
---(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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Getting the week of a date
I am sorry. I didn't read the doc properly. I understood why it is written
in that way.
Sorry for the trouble. Thanks. It worked fine for me.
Thanks
Kumar
- Original Message -
From: "Kumar" <[EMAIL PROTECTED]>
To: "sad" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, February 17, 2004 10:40 AM
Subject: Re: [SQL] Getting the week of a date
> Seems a part of your function always returns '0'
> select 1 - (to_char(date_trunc('month',now()),'D'))::INT2
>
> because while we use date_trunc it will always return the first day of the
> month and when it get subtracted by '1' it be always zero. Is there any
> reason why you have included that?
>
> Thanks
> Kumar
>
> - Original Message -
> From: "sad" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Monday, February 16, 2004 6:53 PM
> Subject: Re: [SQL] Getting the week of a date
>
>
> EXCUSE ME, GUYS !
>
> i forgot to add one monome:
> 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4)
> which is stands for skip a first week of month in case it is not consist
Wed
>
> finally the select will be similar the following
>
> SELECT 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4) + 1 -
> (to_char(date_trunc('month',now()),'D'))::INT2 + 7*3-3 ;
>
> 4 - is a number of Wed in a week (in postgresql numeration)
>
>
>
> ---(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
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
