How to extract microsecs from timestamp.

Dev Shed Forums Sponsor:

April 3rd, 2012, 08:48 AM

omm

Registered User

Join Date: Jul 2010

Posts: 6

Time spent in forums: 1 h 15 m 2 sec
Reputation Power: 0

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

Similarly,
I am trying to figure out the equivalent query for Postgres.

Thanks,
Om

April 3rd, 2012, 09:16 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,685

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 59 m 12 sec
Reputation Power: 284

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....

http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

Code:

SELECT extract(microseconds FROM CRT_TMS) 
FROM TABLEA

April 3rd, 2012, 12:36 PM

omm

Registered User

Join Date: Jul 2010

Posts: 6

Time spent in forums: 1 h 15 m 2 sec
Reputation Power: 0

Thanks for the link

Here is example I found there
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
Result: 28500000

Quote:

Originally Posted by shammat

Code:

SELECT extract(microseconds FROM CRT_TMS) 
FROM TABLEA

April 3rd, 2012, 01:40 PM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,685

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 59 m 12 sec
Reputation Power: 284

You'll need to apply some maths on the result. It's probably easier to wrap that into a function

Something like this:

Code:

create or replace function microseconds(timestamp)
returns numeric
as
$$
  select ((extract(seconds from $1) - trunc(extract(seconds from $1))) * 1000000)::numeric;
$$
language sql
immutable;

Viewing: Dev Shed Forums > Databases > PostgreSQL Help > How to extract microsecs from timestamp.

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump