Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA70134 for ; Wed, 23 Feb 2000 12:12:49 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id MAA04754; Wed, 23 Feb 2000 12:12:16 -0500 (EST) To: Robin Keech cc: "'pgsql-sql@postgresql.org'" Subject: Re: [SQL] capturing microseconds & computing difference between two dates In-reply-to: <31262893C5E5D2119C4900105A30B3A112D59E@roadrunner.dialogue.net> References: <31262893C5E5D2119C4900105A30B3A112D59E@roadrunner.dialogue.net> Comments: In-reply-to Robin Keech message dated "Wed, 23 Feb 2000 14:25:25 +0000" Date: Wed, 23 Feb 2000 12:12:16 -0500 Message-ID: <4751.951325936@sss.pgh.pa.us> From: Tom Lane Robin Keech writes: > INSERT INTO table (Start_Date, End_Date) VALUES ('2000-01-01 > 10:01:00.123456' , '2000-01-01 10:01:00.654321') > What I want is to get the difference between these two times - normally > only microseconds. > However, PostgreSQL 6.5 doesn't seem to hold the microsecond portion, or > if it does it does not display it. The default datetime display formats are not set up for more than two decimal places, but there is more resolution in there. date_part() will pull out a float for you --- try select '2000-01-01 10:01:00.123456'::datetime - '2000-01-01 10:01:00.654321'::datetime; ?column? ----------------- 00:00:00.53 ago (1 row) select date_part('seconds', '2000-01-01 10:01:00.123456'::datetime - '2000-01-01 10:01:00.654321'::datetime); date_part ----------- -0.530865 (1 row) Either date_part('seconds') or date_part('epoch') is probably what you want. BTW, the underlying representation is float8 seconds before/since the epoch, which I think is 1/1/2000 --- so you only get "microsecond" resolution up to about 100 years away from the present. regards, tom lane