April 3rd, 2012, 09:48 AM
How to extract microsecs from timestamp.
In DB2 I was able to extract microsecs with the query
"SELECT MICROSECOND(CRT_TMS) FROM TABLEA"
So, for time stamp '2006/01/19 12:08:18.123456', the microsec will be 123456
I am trying to figure out the equivalent query for Postgres.
April 3rd, 2012, 10:16 AM
I agree this is really hard to find in the manual.
It's hidden behind the heading "Date/Time Functions and Operators", who would have guessed that....
SELECT extract(microseconds FROM CRT_TMS)
April 3rd, 2012, 01:36 PM
Thanks for the link
Here is example I found there
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
Originally Posted by shammat
April 3rd, 2012, 02:40 PM
You'll need to apply some maths on the result. It's probably easier to wrap that into a function
Something like this:
create or replace function microseconds(timestamp)
select ((extract(seconds from $1) - trunc(extract(seconds from $1))) * 1000000)::numeric;