From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Robin Keech <robin(at)dialogue(dot)co(dot)uk> |
Cc: | "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: [SQL] capturing microseconds & computing difference between two dates |
Date: | 2000-02-23 17:12:16 |
Message-ID: | 4751.951325936@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Robin Keech <robin(at)dialogue(dot)co(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Rolf Staege | 2000-02-24 08:06:31 | create a database |
Previous Message | Justin Long | 2000-02-23 14:35:05 |