pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcapturing microseconds & computing difference between two dates
2+ messages / 2 participants
[nested] [flat]
* capturing microseconds & computing difference between two dates
@ 2000-02-23 14:25 Robin Keech <robin@dialogue.co.uk>
0 siblings, 1 reply; 2+ messages in thread
From: Robin Keech @ 2000-02-23 14:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] capturing microseconds & computing difference between two dates
@ 2000-02-23 17:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robin Keech <robin@dialogue.co.uk>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2000-02-23 17:12 UTC (permalink / raw)
To: Robin Keech <robin@dialogue.co.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-02-23 17:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-23 14:25 capturing microseconds & computing difference between two dates Robin Keech <robin@dialogue.co.uk>
2000-02-23 17:12 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox