Thread: capturing microseconds & computing difference between two dates

Hi,  I'm pulling my hair out.  Can anyone help?

I have a table with start and end dates ... type : TimeStamp.  (offers
microsecond resolution according to the docs)
I insert datetime information with microsecond resolution 
ie
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.

I have set the DateStyle to SQL -> this should stop the default ISO
Format from displaying the timezone difference on the end of the
timestamp (ie +00), but it doesn't.

DateTime is no good for me becasue it only offers millisecond precision
(ie 2 decimal places).

Can anyone help?  Should I just relax and get used to counting sunsets
instead?

Thanks
Robin

Robin Keech <robin@dialogue.co.uk> 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