Обсуждение: a question about dates and timestamp
hi all i have a table like this id_message int4 not null default nextval ( ... id_abonne int4 texte_message varchar() not null date_message numeric not null the default size of numeric is 30.6 another table : id_message int4 id_abonne int4 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 : 965340000 = 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
> 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
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.000000": can't parse ".000000" 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
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.000001'::int; ERROR: pg_atoi: error in "965293003.000001": can't parse ".000001" test=# select '965293003.000001'::numeric(9, 0);?column? -----------965293003 (1 row) test=# select '965293003.000001'::numeric(9, 0)::int;?column? -----------965293003 (1 row) test=# select abstime('965293003.000001'::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?
Ange Michel POZZO <poange@technologist.com> 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.000000": can't parse ".000000" 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