[SQL] a question about dates and timestamp
hi all i have a table like this id_message int4 not null default nextval ( ... id_abonneint4 texte_message varchar() not null date_message numeric not null the default size of numeric is 30.6 another table : id_message int4 id_abonneint4 text_message text date_message int4 in both case date_message is used to store a unix timestamp ( number of seconds since 1st of 1970) for example : 96534 = 2000-08-03 00:00:00+02 my question is how can i convert the numeric or the int4 value to a date value? which function should i use to make something like this work ? select convert_to_date( date_message) from my_table; 2000-08-03 00:00:00+02 2000-08-01 13:56:00+02 etc etc thanks in advance Pozzo Ange
Re: [SQL] a question about dates and timestamp
> my question is how can i convert the numeric or the int4 value to > a date value? test=# select abstime(965293003); abstime 2000-08-03 10:56:43+02 (1 row) Karel
Re: [SQL] a question about dates and timestamp
Le jeu, 03 aoû 2000, Karel Zak a écrit : > > my question is how can i convert the numeric or the int4 value to > > a date value? > > > test=# select abstime(965293003); > abstime > > 2000-08-03 10:56:43+02 > (1 row) > > Karel this is the result of the query for me : test=> select abstime(965293003); ?column? - 965293003 (1 row) i must do : select datetime(abstime( value )); if value is a int4 work ok but for a numeric, it don't work i have tried to convert a numeric to int4 with floor, int4, numeric_int4 nothing work ! select datetime(abstime( floor (value) )); select datetime(abstime( int4 (value) )); select datetime(abstime( numeric_int4 (value) )); in all case : ERROR: pg_atoi: error in "952969611.00": can't parse ".00" what is the way to convert numeric to int4 ? i know this is'nt a good choise to store timestamp in a int4 or numeric value, but i found the database like this and know i can't change this :( thanks POZZO ange
Re: [SQL] a question about dates and timestamp
On Thu, 3 Aug 2000, Ange Michel POZZO wrote:
> Le jeu, 03 aoű 2000, Karel Zak a écrit :
> > > my question is how can i convert the numeric or the int4 value to
> > > a date value?
> >
> >
> > test=# select abstime(965293003);
> > abstime
> >
> > 2000-08-03 10:56:43+02
> > (1 row)
> >
> > Karel
>
>
> this is the result of the query for me :
>
> test=> select abstime(965293003);
> ?column?
> -
> 965293003
> (1 row)
Ooops I use CVS version, but you have probably 6.5...
Sorry, Now I haven't time check what is 7.1 / 7.0 / 6.5 feature.
in current code:
test=# select '965293003.01'::int;
ERROR: pg_atoi: error in "965293003.01": can't parse ".01"
test=# select '965293003.01'::numeric(9, 0);
?column?
---
965293003
(1 row)
test=# select '965293003.01'::numeric(9, 0)::int;
?column?
---
965293003
(1 row)
test=# select abstime('965293003.01'::numeric(9, 0)::int);
abstime
2000-08-03 10:56:43+02
(1 row)
BTW. The postgreSQL has good date/time support; why you use slow and
expensive numeric?
Re: [SQL] What is happening?
On Thu, 3 Aug 2000, Carolyn Lu Wong wrote: > I was trying to dump data, drop then recreate table, and import data > back to the table. There were errors during the process and the > transaction was not committed. > > The table is there when I do '\d' and the table definition is there. > However, I can't drop table nor select from this table. I always get the > following error: > > ERROR: mdopen: couldn't open accounts: No such file or directory. what version of PostgreSQL are you running, first off ... what I've done in the past that has been successful is to go into the data/base/ directory, do a 'touch accounts' so that there is a file there for the postgres process to unlink, and then do the drop table ...
[SQL] Extracting data by months
Lo. I'm in dire need of knowledge, how to extract data by month. Monthday and year arent' important, those I can't give from perl script, but what I do give to postgres are the numbers of the months. Date field is in timestamp. I thought about date_trunc, but I can't think of, how to get data with it. In script will be 12 queries, for each month, to get the news from that month. Btw, is there somewhere a manual about date_trunc, the docs that come with RH6.1 distribution are somewhat short and lacking explanation of date_trunc. Antti
Re: [SQL] What is happening?
Carolyn Lu Wong <[EMAIL PROTECTED]> writes: > I was trying to dump data, drop then recreate table, and import data > back to the table. There were errors during the process and the > transaction was not committed. > ERROR: mdopen: couldn't open accounts: No such file or directory. Rolling back a "drop table" doesn't work at the moment :-(, because the physical table file is deleted at the moment of DROP. When you aborted the transaction, the system-table rows for the table came back to life, but the file didn't. You can get back into a consistent state by creating a dummy table file by hand, eg do touch .../data/base/yourdb/accounts and then you will be able to do the DROP TABLE. After that you can recreate and reload the table from the data file (which I hope you kept...) One of the things on the to-do list is to postpone physical delete of table files till COMMIT, so that a DROP can be rolled back safely. (Not likely for 7.1, but maybe for 7.2.) In the meantime, it's probably best not to do DROP inside a transaction. 7.0 will emit a notice warning you about this, but earlier versions don't. BTW, ALTER TABLE RENAME in a transaction is equally dangerous for the same kind of reason. regards, tom lane
Re: [SQL] a question about dates and timestamp
Ange Michel POZZO <[EMAIL PROTECTED]> writes: > select datetime(abstime( floor (value) )); > select datetime(abstime( int4 (value) )); > select datetime(abstime( numeric_int4 (value) )); > in all case : > ERROR: pg_atoi: error in "952969611.00": can't parse ".00" That was fixed in January. Update to 7.0 and it'll work fine. You might be able to make it work in 6.5 like this: abstime(int4(float8(numeric))) regards, tom lane
[SQL] SQL (table transposition)
Is there any way to get table T3 (below) from T1 and T2 using SQL (select, view, etc)? T3 is basically all INDEX values from T1 matched to IND from T2 with the corresponding KEY/VALUE pairs transposed from rows to columns. --- |INDEX| (T1) --- | 1 | | 2 | | 3 | --- - |IND|KEY| VALUE | (T2) - | 1 | 1 | val_a | | 1 | 2 | val_b | | 1 | 3 | val_c | | 2 | 1 | val_d | | 2 | 2 | val_e | | 3 | 1 | val_f | | 3 | 3 | val_g | - -- |T1_INDEX|KEY1VAL|KEY2VAL|KEY3VAL| (T3) -- | 1| val_a | val_b | val_c | | 2| val_d | val_e | | | 3| val_f | | val_g | -- Thanks for any suggestions med vänlig hälsning /Dana
Re: [SQL] SQL (table transposition)
Hope you like black magic :) SELECT IND AS T1_INDEX, MIN(CASE WHEN KEY=1 THEN VALUE ELSE NULL END) AS KEY1VAL, MIN(CASE WHEN KEY=2 THEN VALUE ELSE NULL END) AS KEY2VAL, MIN(CASE WHEN KEY=3 THEN VALUE ELSE NULL END) AS KEY3VAL FROM T2 GROUP BY IND ORDER BY IND; Mark [EMAIL PROTECTED] wrote: > > Is there any way to get table T3 (below) from T1 and T2 using SQL (select, view, >etc)? > > T3 is basically all INDEX values from T1 matched to IND from T2 with the >corresponding KEY/VALUE pairs transposed from rows to columns. > > --- > |INDEX| (T1) > --- > | 1 | > | 2 | > | 3 | > --- > > - > |IND|KEY| VALUE | (T2) > - > | 1 | 1 | val_a | > | 1 | 2 | val_b | > | 1 | 3 | val_c | > | 2 | 1 | val_d | > | 2 | 2 | val_e | > | 3 | 1 | val_f | > | 3 | 3 | val_g | > - > > -- > |T1_INDEX|KEY1VAL|KEY2VAL|KEY3VAL| (T3) > -- > | 1| val_a | val_b | val_c | > | 2| val_d | val_e | | > | 3| val_f | | val_g | > -- > > Thanks for any suggestions > > med vänlig hälsning > /Dana
[SQL] Re: PL/pgSQL
Oh yeah, I'm using v7.0 Mark
[SQL] PL/pgSQL evaluation order
Hi, I have trigger function with the statement: IF TG_OP=''INSERT'' OR NEW.ip!=OLD.ip THEN expecting the TG_OP=''INSERT'' to be evaluated first, and if true, proceed with the body. But it gets evaluated as the result of an INSERT statement, I get a (rather strangly worded) error: ERROR: record old is unassigned yet which means that the second statement is still being evaluated even though the first is true. Assuming that this is the result of the parser/planner messing with that statement, is there a way to force evaluation order without having to go: IF TG_OP=''INSERT'' THEN ELSE IF NEW.OP!=OLD.ip THEN END IF; END IF; Thanks, Mark
[SQL] A question about indexes...
Hello, I have the following tables in my db Painter (id integer, uri varchar(256)) and paints (id1 integer, id2 integer) I want to optimize the question select id from Painter where uri = 'x'; What kind of index (Btree or Hash) is more efficient to create on field uri since it's a string? I also want to optimize the join between the ables Painter and paints on the fields id and id1 respectively? I can either define the field id as a Primary Key or create an Btree index on it. What is more effient?? >From my test I see that creating Btree index is a bit faster!!. Would the performance (of the join) be improved if I created indexes both on field id and id1 or it's sufficient to create one of the two indexes ? As far as I can see the performance is improved if I have a Primary Key on Painter.id and a BTree index on paints.id1. However when I create a Btree index on Painter.id and a BTree index on paints.id1 performance gets worst. thank you in advance for your help Sofia Alexaki [EMAIL PROTECTED]
Re: [SQL] Extracting data by months
This might seem rather silly, but could you simply do something like: select * from database where date_field >= '01/01/2000'::date and date_field < '02/01/2000'::date; Of course, if date_field could contain many different years, then this would not get you the result you wanted. John On Thu, 3 Aug 2000, Antti Linno wrote: > Lo. > > I'm in dire need of knowledge, how to extract data by month. Monthday > and year arent' important, those I can't give from perl script, but what > I do give to postgres are the numbers of the months. Date field is in > timestamp. I thought about date_trunc, but I can't think of, how to get > data with it. In script will be 12 queries, for each month, to get the > news from that month. > Btw, is there somewhere a manual about date_trunc, the docs that come with > RH6.1 distribution are somewhat short and lacking explanation of > date_trunc. > Antti > >
Re: [SQL] A question about indexes...
Alexaki Sofia <[EMAIL PROTECTED]> writes: > I can either define the field id as a Primary Key or create an Btree index > on it. What is more effient?? > From my test I see that creating Btree index is a bit faster!!. I think you're seeing things. Declaring a field primary key creates a btree index on it (and also enables UNIQUE and NOT NULL checks, but those don't affect the speed of lookups). There isn't going to be any difference between the two ways of doing it --- whatever difference you measured was due to other factors, eg, disk pages already in cache. As for your other point I'd generally recommend btree over hash indexes. The btree code is much more thoroughly tested, supports concurrent updates which hash indexes don't, and allows order-based index scans which hash doesn't. I don't see any redeeming social value in a hash index, actually... regards, tom lane
Re: [SQL] PL/pgSQL evaluation order
Doesn't seem like a bad idea. I did find a better workaround, though - cond:=TG_OP=''INSERT''; IF cond=FALSE THEN cond:=NEW.ip!=OLD.ip; END IF; IF cond=TRUE ... so it won't kill me. Anyway, I've been totally impressed with the 7.0 release. I'm building the EPA IP address registration system on top of it. My favorite feature is the support for REFERENCES (though I had to hack the code a bit to get around a permissions problem). Thanks, Mark Jan Wieck wrote: > > Maybe we should change the treatment of unassigned rowtype > values that any reference to it holds the NULL value? >
Re: [SQL] Extracting data by months
Actually, PostgreSQL has specific function for this purpose. The query would
be:
SELECT * FROM table WHERE date_part('month', date_field) = '8';
(example to select month 8)
If you use date_trunc, the selection will be by month/year - that is,
date_trunc('month', date_field) will always result in the 1st monthday of the
month. The year will be preserved.
Daniel
>>>John McKown said:
> This might seem rather silly, but could you simply do something like:
>
> select * from database
>where date_field >= '01/01/2000'::date
>and date_field < '02/01/2000'::date;
>
> Of course, if date_field could contain many different years, then this
> would not get you the result you wanted.
>
> John
>
> On Thu, 3 Aug 2000, Antti Linno wrote:
>
> > Lo.
> >
> > I'm in dire need of knowledge, how to extract data by month. Monthday
> > and year arent' important, those I can't give from perl script, but what
> > I do give to postgres are the numbers of the months. Date field is in
> > timestamp. I thought about date_trunc, but I can't think of, how to get
> > data with it. In script will be 12 queries, for each month, to get the
> > news from that month.
> > Btw, is there somewhere a manual about date_trunc, the docs that come with
> > RH6.1 distribution are somewhat short and lacking explanation of
> > date_trunc.
> > Antti
> >
> >
>
[SQL] Recursive SQL
Dear all, Is postgresql supporting recursive sql? If no, how can my sql to change for recursive function ( on 4 or 5 levels are needed ) ? Many thanks! Best regards, Boris
